I'm trying to sum a group of numbers based on two variables. The SumIf
statement isn't working right, neither is SumProduct. Can someone help?
Here's what the formula should calculate:

IF (B2:B1589="ICM") and IF (C2:C1589="Yes"), then SUM (E2:E1589)


--
jlhart76
------------------------------------------------------------------------
jlhart76's Profile: http://www.excelforum.com/member.php...o&userid=30274
View this thread: http://www.excelforum.com/showthread...hreadid=499413

Hi

Try
=SUMPRODUCT(--(B2:B1589="BCN"),--(C2:C1589="Yes"),E2:E1589)
assuming values in E2:E1589 are numeric.

--
Regards

Roger Govier


"jlhart76" wrote
in message ...

I'm trying to sum a group of numbers based on two variables. The SumIf
statement isn't working right, neither is SumProduct. Can someone
help?
Here's what the formula should calculate:

IF (B2:B1589="ICM") and IF (C2:C1589="Yes"), then SUM (E2:E1589)


--
jlhart76
------------------------------------------------------------------------
jlhart76's Profile:
http://www.excelforum.com/member.php...o&userid=30274
View this thread:
http://www.excelforum.com/showthread...hreadid=499413

This works for me...
=SUMPRODUCT((B2:B1589="ICM")*(C2:C1589="YES")*(D3: D6))

- John
www.JohnMichl.com

Try to use array function (press ctrl+shift+enter after input the formula)

=SUM(IF((B2:B1589="ICM")*(C2:C1589="Yes"),E2:E1589 ))

Yongjun CHEN
Project Oriented Spreadsheet Development and Consulting
- http://www.geocities.com/udqservices/UDQConsulting.htm

=================================
"jlhart76" wrote in
message ...

I'm trying to sum a group of numbers based on two variables. The SumIf
statement isn't working right, neither is SumProduct. Can someone help?
Here's what the formula should calculate:

IF (B2:B1589="ICM") and IF (C2:C1589="Yes"), then SUM (E2:E1589)


--
jlhart76
------------------------------------------------------------------------
jlhart76's Profile:
http://www.excelforum.com/member.php...o&userid=30274
View this thread: http://www.excelforum.com/showthread...hreadid=499413

I'd bet it doesn't, not with unequal ranges. #N/A is more likely.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Michl" wrote in message
oups.com...
This works for me...
=SUMPRODUCT((B2:B1589="ICM")*(C2:C1589="YES")*(D3: D6))

- John
www.JohnMichl.com

Thanks for the catch Bob. D3:D6 should be D2:D1589. I hadn't finished
editing my test formula.

- John

Hi

Where on earth did I get BCN from?
You were looking for ICM.
The formula should be (of course)
=SUMPRODUCT(--(B2:B1589="ICM"),--(C2:C1589="Yes"),E2:E1589)


--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi

Try
=SUMPRODUCT(--(B2:B1589="BCN"),--(C2:C1589="Yes"),E2:E1589)
assuming values in E2:E1589 are numeric.

--
Regards

Roger Govier


"jlhart76"
wrote in message
...

I'm trying to sum a group of numbers based on two variables. The
SumIf
statement isn't working right, neither is SumProduct. Can someone
help?
Here's what the formula should calculate:

IF (B2:B1589="ICM") and IF (C2:C1589="Yes"), then SUM (E2:E1589)


--
jlhart76
------------------------------------------------------------------------
jlhart76's Profile:
http://www.excelforum.com/member.php...o&userid=30274
View this thread:
http://www.excelforum.com/showthread...hreadid=499413

It worked! Thanks so much.

& thanks to everyone else for helping me, I appreciate it.

Roger Govier Wrote:
Hi

Try
=SUMPRODUCT(--(B2:B1589="BCN"),--(C2:C1589="Yes"),E2:E1589)
assuming values in E2:E1589 are numeric.

--
Regards

Roger Govier



--
jlhart76
------------------------------------------------------------------------
jlhart76's Profile: http://www.excelforum.com/member.php...o&userid=30274
View this thread: http://www.excelforum.com/showthread...hreadid=499413