Help With Nested SumIf / Or
 

Hello -

I am trying to sum a column based on criteria in 2 other columns, and
I need help with the formula.

I need to sum the contents of cells H4 through H200 if Cells C4
through C200 equal "Special Project", and if Cells D4 through D200
equal "New" or "In Progess". A sample is shown below.


Special Project New 3
Special Project In Progress 2
New In Progress 5
Special Project Deployed 7

The expected result is 5.


Thanks

Hi, Give the formula below a try.

=SUMPRODUCT(--(C4:C200="Special Project")*(D4: D200="New")+(C4:C200="Special Project")*(D4: D200="In Progress"),H4:H200)

But with no spaces in the middle...

Help With Nested SumIf / Or
 

"foofoo" wrote:
I need to sum the contents of cells H4 through H200
if Cells C4 through C200 equal "Special Project", and
if Cells D4 through D200 equal "New" or "In Progess".


=SUMPRODUCT((C4:C200="special project")*(D4:D200={"new","in
progress"})*H4:H200)

Help With Nested SumIf / Or
 

PS.... I wrote:
"foofoo" wrote:
I need to sum the contents of cells H4 through H200
if Cells C4 through C200 equal "Special Project", and
if Cells D4 through D200 equal "New" or "In Progess".


=SUMPRODUCT((C4:C200="special project")*(D4:D200={"new","in
progress"})*H4:H200)

I see that got word-wrapped in an odd way, at least in my view of it. The
following might be more reliable to copy-and-paste:

=SUMPRODUCT((C4:C200="special project")
*(D4:D200={"new","in progress"})*H4:H200)

But I confess: I was just trying to show how compact it can be written. I
would write the following, which might be easier to extend as needed in
future situations:

=SUMPRODUCT((C4:C200="special project")
*((D4:D200="new")+(D4:D200="in progress")0),H4:H200)

Help With Nested SumIf / Or
 

On Friday, June 22, 2012 5:54:57 PM UTC-5, foofoo wrote:
Hello -

I am trying to sum a column based on criteria in 2 other columns, and
I need help with the formula.

I need to sum the contents of cells H4 through H200 if Cells C4
through C200 equal "Special Project", and if Cells D4 through D200
equal "New" or "In Progess". A sample is shown below.


Special Project New 3
Special Project In Progress 2
New In Progress 5
Special Project Deployed 7

The expected result is 5.


Thanks



Worked perfectly! Thanks for your help.