• Status: Solved
• Priority: Medium
• Security: Public
• Views: 404

Excel 2000 - SUMIF using

Dear Experts,

I have a SUMIF function below which works fine, it summarizes the I column values from Sheet2, where in column D are "Car1" categories

=SUMIF(Sheet2!D:D;"Car1";Sheet2!I:I)

Could you advise is it possible to have the condition more complex, so SUMIF the Sheet2 I column values, where column D has "Car1" AND where column E has "Red"?

Briefly so do a SUMIF where D is "Car1" AND where E is "Red"

thanks,
0
csehz
• 4
• 2
1 Solution

Commented:
Try:

=SUMPRODUCT((Sheet2!D1:D65535="Car1", (Sheet2!E1:E65535="Red";Sheet2!I1:I65535)
0

Commented:
Oops - should have been, try

=SUMPRODUCT((Sheet2!D1:D65535="Car1")*(Sheet2!E1:E65535="Red")*Sheet2!I1:I65535)
0

IT consultantAuthor Commented:
Patrickab thanks, I have tried but somehow the formula

=SUMPRODUCT((Sheet2!D1:D65535="Car1");(Sheet2!E1:E65535="Red");Sheet2!I1:I65535)

gives 0 for me.

Could you maybe have a short look in the attached example file, I have tried it in cell B1.

thanks,
SumproductExample.xls
0

Commented:
Not for points:

You missed a bit of Patricks formula ...

=SUMPRODUCT((Sheet2!D1:D65535="Car1")*(Sheet2!E1:E65535="Red");Sheet2!I1:I65535)

The first ";" should be a "*"

Chris
0

IT consultantAuthor Commented:
Chris thanks it works, at my computer the "," used to be ";" so I am never sure so just trying.

Thanks very much to both of you
0

Commented:
csehz,

Check out the attached file. It's working OK in there!

Patrick
csehz-01.xls
0

Commented:
csehz - Thanks for the grade - Patrick
0

Featured Post

• 4
• 2
Tackle projects and never again get stuck behind a technical roadblock.