Help With Nested SumIf / Or

[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

 

[Spencer101]

Hello

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

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


Special Project New
Special Project In Progress
New In Progress
Special Project Deployed

The expected result is 5


Thank

Hi, Give the formula below a try

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

But with no spaces in the middle..

 

[joeu2004]

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")*(D4200={"new","in
progress"})*H4:H200)

 

[joeu2004]

=SUMPRODUCT((C4:C200="special project")*(D4200={"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")
*(D4200={"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")
*((D4200="new")+(D4200="in progress")>0),H4:H200)

 

[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

Worked perfectly! Thanks for your help.