Excel formula Help.

  cortez© 13:11 10 Feb 2007
Locked

Hi,

I have a an excel file set out with the following Field headings:

CAR Num. used REVENUE.
1 £60
2 £70
1 £60
4 £50
1 £60

Without using macros and only a formula how could i how much each car has earnt in revenue. I could achieve this fairly easily using a bit of VB for a macro, but has anyone got an idea how to achieve it much simply through the use of some sort of excel formula/function.

Thanks for any info.

  VoG II 13:16 10 Feb 2007

Do you mean that in the example for Car 1 you want to return £180?

If the table is in A1:B6 with headings in row 1

=SUMIF(A2:A6,1,B2:B6)

will sum the £s for Car 1.

  Noldi 13:21 10 Feb 2007

Sorry very busy dont have time to look but cant you do a auto filter then have the total of the filtered cells.

Noldi

  cortez© 13:21 10 Feb 2007

Ah! Thank you!

  cortez© 13:21 10 Feb 2007

.....VoG s solution was what i needed.

  Noldi 14:12 10 Feb 2007

Using Car Num in A and Revenue in B
AutoFilter the Data
Then above Revenue EG:B1 Enter SUBTOTAL(9,B2:B500)
So when you filter a Car Num then you a total for the filtered data

That was my way of thinking

Noldi

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

The art of 'British' pulp fiction

Best password managers for Mac

TV & streaming : comment regarder le Tournoi des Six Nations 2018 ?