Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
|
|||
|
|||
Quote:
=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... Last edited by Spencer101 : June 23rd 12 at 07:04 AM |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
nested sumif? | Excel Worksheet Functions | |||
nested sumif | Excel Discussion (Misc queries) | |||
SUMIF nested Formula | Excel Discussion (Misc queries) | |||
Nested SumIf statement | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions |