Another SUMIF question

  • Thread starter tawtrey(remove this )
  • Start date
T

tawtrey(remove this )

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!
 
T

tawtrey(remove this )

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...
 
K

Kevin Vaughn

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
 
M

Marcelo

try ","

regards from Brazil
Marcelo


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

SteveG

Try this,

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

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


HTH

Steve
 
T

tawtrey(remove this )

=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.
 
K

Kevin Vaughn

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.
 
T

tawtrey(remove this )

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.
 
Top