Solved

# Excel 2000 - SUMIF using

Posted on 2011-03-06
339 Views
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
Question by:csehz
• 4
• 2

LVL 45

Expert Comment

Try:

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

LVL 45

Accepted Solution

patrickab earned 500 total points
Oops - should have been, try

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

LVL 1

Author Comment

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

LVL 59

Expert Comment

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

LVL 1

Author Comment

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

LVL 45

Expert Comment

csehz,

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

Patrick
csehz-01.xls
0

LVL 45

Expert Comment

csehz - Thanks for the grade - Patrick
0

## Featured Post

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.