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

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
Asked:
csehz
  • 4
  • 2
1 Solution
 
patrickabCommented:
Try:

=SUMPRODUCT((Sheet2!D1:D65535="Car1", (Sheet2!E1:E65535="Red";Sheet2!I1:I65535)
0
 
patrickabCommented:
Oops - should have been, try

=SUMPRODUCT((Sheet2!D1:D65535="Car1")*(Sheet2!E1:E65535="Red")*Sheet2!I1:I65535)
0
 
csehzIT 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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Chris BottomleyCommented:
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
 
csehzIT 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
 
patrickabCommented:
csehz,

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

Patrick
csehz-01.xls
0
 
patrickabCommented:
csehz - Thanks for the grade - Patrick
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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