SUMIF Formula Problem

  • Thread starter Thread starter albertmb
  • Start date Start date
A

albertmb

Hi to all,
Wonder if you can help me. I have the following formula which works ok, is
it possible to have a similar formula but with extended choice of cells:

SUMIF('1'!A:A,A5,'1'!J:J)

Example: The present formula will compare ‘1’!A:A with A5, I would like a
formula to compare ‘1’!A:A,A5 and ‘1’!B:B giving the same result from ‘1’J:J

‘1’!A:A being a category like “Travel Expensesâ€,
‘1’!B:B being a department like “Sales Departmentâ€
‘1’!J:J being the “Costâ€

Thank You

Albert
 
Hello.
If you are a XL2007 user, use SUMIFS, otherwise, give a try to :
=SUMPRODUCT(('1'!A:A=A5)*('1'!B:B=A6)*'1'!J:J)
HTH
Daniel
 
Try the following pattern:

=SUMPRODUCT(--(‘1’!A:A="CRITERIA 1")*--(‘1’!B:B="CRITERIA
2")*--(‘1’!C:C="CRITERIA 3",‘1’!J:J))

It would give you the total of amounts in J of the rows which have the cells
in Column A, Column B AND Column C met.
 
Thank you for your fast reply, but the formula is giving me the #NUM error.
Can it be I misunderstood something?

Regards
Albert
 
Thank you for your fast reply, but the formula is giving me the #NUM error.
Can it be I misunderstood something?

Regards
Albert
 
Thank you for your fast reply, but the formula is giving me the #NUM error.
Can it be I misunderstood something?

Regards
Albert
 
Sorry,

A Typo Mistake, consider the revised one. Only a place of closing parentesis
changed.

=SUMPRODUCT(--('1'!A:A="CRITERIA 1")*--('1'!B:B="CRITERIA
2")*--('1'!C:C="CRITERIA 3"),'1'!J:J)
 
Thank You Faraz now it worked perfectly, but the actual problem was the
ranges, take note of what Dave Peterson remarked regarding non XL 2007 useres.
Thank you once again for your interest
 
Back
Top