Help With Nested SumIf / Or

  • Thread starter Thread starter foofoo
  • Start date Start date
F

foofoo

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
 
foofoo said:
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".
Click to expand...


=SUMPRODUCT((C4:C200="special project")*(D4:D200={"new","in
progress"})*H4:H200)
 
PS.... I said:
=SUMPRODUCT((C4:C200="special project")*(D4:D200={"new","in
progress"})*H4:H200)
Click to expand...

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)
 
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
Click to expand...



Worked perfectly! Thanks for your help.
 
Back
Top