Another SUMIF question

[Guest]

My book is telling me that I can't use multiple conditions with a SUMIF
statement but other sources are telling me I can. Either way, it's not
working!

My situation:

Look at data in $V$2:$V$144 (range = "group"). If cells in "group" =
"Broth" then look at $Y$2:$Y$144 (range = "bldg"). If "bldg" = "1" then sum
corresponding cells in $R$2:$R$144 (range = "fcst").

This is one of my attempts - feeble...

=SUM(IF(group="Broths",IF(bldg="1",fcst,0)))

Help, please!

 

[Ardus Petus]

=SUMPRODUCT((group="Broth")*(bldg="1");fcst)

HTH

 

[Guest]

So I am trying the SUMPRODUCT which I have as this:

=SUMPRODUCT((group="Broths")*(bldg="1"),fcst)

But I am returning values of 0 which is not correct. I think the problem is
when I am then telling excel to add up what's in "fcst."

I did check my range name and they do match (i just mis-typed). I also
tried with an array w/ Ctrl+Shift+Enter.

Suggestion?

Thanks...

 

[Guest]

It's not accepting my formula with the ";" before fcst.

 

[Guest]

That may very well work, but it is an array formula so needs to be entered by
using ctrl-shift-enter (not just enter). Also you state Broth in the
description and Broths in the formula, perhaps just a typo? You may also
want to check out Bob Phillip's white paper which explains how to use
sumproduct. It made a believer out of me.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Guest]

try ","

regards from Brazil
Marcelo


"tawtrey(remove this )@pacificfoods.com" escreveu:

 

[SteveG]

Try this,

=SUMPRODUCT((group="Broth")*(bldg=1)*fcst)

No quotes around the 1 and a * in place of your ;


HTH

Steve

 

[Guest]

=SUMPRODUCT(($V$2:$V$144="Broths")*($Y$2:$Y$144=1),$R$2:$R$144)

This is what finally worked. Turned out that I needed to take off the ""
around "1" since it was a numeric, not text value.

Thanks to all of you, especially for that SUMPRODUCT info page.

 

[Guest]

I'd also like to thank Bob for that Sumproduct page. I had no idea how to
use sumproduct until I read that page. Now I feel I have a very good
understanding of how to use it.

 

[Guest]

I just found this and it was a sort of "wizard" for what took me so long to
figure out.

Go to Tools>Add Ins Check Conditional Sum Wizard.

After it installs, go to Tools again and choose Conditional Sum Wizard at
the bottom of the list. The wizard will then walk you through the steps and
you can view the formula afterward if you want to see what the correct
formula looks like.