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!

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

HTH
--
AP

"tawtrey(remove this "
osoft.com a écrit dans le
message de news: ...
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!

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

"Kevin Vaughn" wrote:

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

--
Kevin Vaughn


"tawtrey(remove this " wrote:

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!

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


"Ardus Petus" wrote:

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

HTH
--
AP

"tawtrey(remove this "
osoft.com a écrit dans le
message de news: ...
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!

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

--
Kevin Vaughn


"tawtrey(remove this " wrote:

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!

try ","

regards from Brazil
Marcelo


"tawtrey(remove this " escreveu:

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


"Ardus Petus" wrote:

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

HTH
--
AP

"tawtrey(remove this "
osoft.com a écrit dans le
message de news: ...
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!

Try this,

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

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


HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=547175

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




"tawtrey(remove this " wrote:

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!

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.
--
Kevin Vaughn


"tawtrey(remove this " wrote:

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




"tawtrey(remove this " wrote:

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!

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

Go to ToolsAdd 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.