SUMIFS error

[Guest]

Price Type AGENT
$84,000.00 L JONES

All I want to do is get a sum of the price column if type=L and agent =JONES

Here is what I have but I keep getting a Value error??

=SUMIFS(B46:B77,C46:C77,"L",G46:H77,"JONES")

I don't see what it is wrong.
I read one of the posts stating to put a value in each cell to correct the
bug but that did not work either.

 

[Bernard Liengme]

SUMIF is designed to look at one range, compare it to a criteria, then sum
the range or another range.It is not able to handle two criteria; but
SUMPRODUCT is.
=SUMPRODUCT(--(C46:C77="L"),--(G46:H77="JONES"), B46:B77)

For details see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes

 

[Guest]

If you were here I'd kiss you!!

Thank you, thank you!

 

[Bernard Liengme]

It I were there I'd have a red face. Thanks!

 

[Peo Sjoblom]

But sumifs is different, it's a new function in Excel 2007 and the OP is
using it correctly

=SUMIFS(Sum_Range,Criteria_Range1,Criteria1,Criteria_Range2,Criteria2)

I have an example using it over multiple sheet here

http://www.nwexcelsolutions.com/Excel_2007_page.htm


I remember reporting a bug back in 2006 if there were empty cells in the
criteria ranges you would get a value error and they said it would be fixed.
I don't have 2007 where I am at the moment but maybe the OP has a beta
version?

 

[Guest]

I don't have the beta version. I have the full version.

 

[Bernard Liengme]

Time for new glasses! I read SUMIF without the S and I nearly told OP about
the new feature in XL2007

 

[Peo Sjoblom]

The beta was a full version, I just checked on my Excel 2007 at home and I
had no problems using your formula
with blanks in the ranges. What does it say if you click the office button,
then excel options, then resources and about Microsoft office excel 2007?


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[Guest]

Microsoft Office Excel 2007 (12.0.4518.1014)

The beta was a full version, I just checked on my Excel 2007 at home and I
had no problems using your formula
with blanks in the ranges. What does it say if you click the office button,
then excel options, then resources and about Microsoft office excel 2007?


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)