SUMIF Formula Problem

[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

 

[Daniel.C]

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

 

[FARAZ QURESHI]

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.

 

[albertmb]

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

Regards
Albert

 

[albertmb]

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

Regards
Albert

 

[albertmb]

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

Regards
Albert

 

[Dave Peterson]

You can only use the whole column in xl2007. So adjust your ranges.

 

[FARAZ QURESHI]

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)

 

[albertmb]

Thank You Dave it worked perfectly

You can only use the whole column in xl2007. So adjust your ranges.

 

[albertmb]

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