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

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone