Sumif with 2 extra criterias in Excel 2003

[AleloupMadrid]

Hi,

I need to find a Sumif formula compatible whith Excel 2003 with 2 or 3 criterias:
sumif.jpg

The basic formula is
=SUMIF(ART_CollUnit;CALC_CollUnit;ART_FcstO30M)

ART_CollUnit is the column C
CALC_CollUnit is the criteria (egg TCFR08)
ART_FcstO30M is the column X for conditional sum.

However, I also need to select a criteria in column A and B (egg "01" and "1. TRADE")

I can't find a solution based on sumif and AND, MATCH, IF...

Thank you for your help.

[zbor]

Please Login or Register  to view this content.

[AleloupMadrid]

thanks a lot!
So fast...

Let me try this.

[AleloupMadrid]

OK.

This does not work:
=SUMPRODUCT(--($A$1:$A$1000="01");--($B$1:$B$1000="1. Trade");--($C$1:$C$1000="tcfr08"))

Lets me reformulate:

I want to select 1 criteria in col A and 1 criteria in B, THEN compare a criteria in col C with the content of a dynamic cell (ex TCFR08) to sum the amounts of the column X.
It is easy with only col C and X, but I don't know how to add the criterias of col A and B.

Thanks again

[zbor]

Can you upload example workbook (not picture)?
Make sure to avoid personal informations.

[FDibbins]

You did not say what happened when it did not work (error message - wrong total???)

Maybe try replacing the ; with ,

[AleloupMadrid]

Dashboard v3.2.xls

Please find the file attached

[zbor]

I still don't get it.
Where you:

select a criteria in column A and B (egg "01" and "1. TRADE")

?

[AleloupMadrid]

The trick here is to replace the sumifs from excel2007 by something like sumproduct.
Dashboard v3.2.xls

Example:
I want to sum col Y (O30) if 3 conditions are here
- Col A="01"
- Col B="A"
- Col C=dynamic cell B1 in the tab CALC

I hope I'm clear enough

[zbor]

Something like this:

[AleloupMadrid]

I found the solution!

=SUMPRODUCT(--('AR Tracking'!$A$3:$A$10000="01");--('AR Tracking'!$B$3:$B$10000="1. TRADE");--('AR Tracking'!$C$3:$C$10000=CALC!BB1);'AR Tracking'!$Y$3:$Y$10000)

Thanks to this youtube tutorial:
https://www.youtube.com/watch?v=vxpeEf8MYaY