sum of multiple arrays?

Here is an example of my data:


Org criticality
score
Corporate Marketing critical 100
Human Resources critical 100
Corporate Security non-critical 75
Finance non-critical 100
Corporate Marketing critical 50

I need to search column A for a specific org & B for "critical" and sum
their values column C.

Example lookup corporate marketing & critical would add 100+50 = 150


here is the formula I'm using that
is close:

=SUMPRODUCT(SUMIF('Score Calc'!D4:D10,{"Corporate Marketing"},'Score
Calc'!CJ4:CJ10))

I just need to know how to add another part to the array to also look up
"Critical"

=sum(if(A2:A6="Corporate Marketing",if(B2:B6="Critical",C2:C6,"")))


Enter is as Array Function CTRL+SHIFT+ENTER


Thanks


Shail



paula k wrote:
> Here is an example of my data:
>
>
> Org criticality
> score
> Corporate Marketing critical 100
> Human Resources critical 100
> Corporate Security non-critical 75
> Finance non-critical 100
> Corporate Marketing critical 50
>
> I need to search column A for a specific org & B for "critical" and sum
> their values column C.
>
> Example lookup corporate marketing & critical would add 100+50 = 150
>
>
> here is the formula I'm using that
> is close:
>
> =SUMPRODUCT(SUMIF('Score Calc'!D4:D10,{"Corporate Marketing"},'Score
> Calc'!CJ4:CJ10))
>
> I just need to know how to add another part to the array to also look up
> "Critical"

Try toppers' suggestion once more.

paula k wrote:
>
> Here is an example of my data:
>
> Org criticality
> score
> Corporate Marketing critical 100
> Human Resources critical 100
> Corporate Security non-critical 75
> Finance non-critical 100
> Corporate Marketing critical 50
>
> I need to search column A for a specific org & B for "critical" and sum
> their values column C.
>
> Example lookup corporate marketing & critical would add 100+50 = 150
>
> here is the formula I'm using that
> is close:
>
> =SUMPRODUCT(SUMIF('Score Calc'!D4:D10,{"Corporate Marketing"},'Score
> Calc'!CJ4:CJ10))
>
> I just need to know how to add another part to the array to also look up
> "Critical"

--

Dave Peterson

As posted in reply to your previous posting: it's your choice as whether you
use this but it's much easier than using SUMIF.

Use the following rather then SUMIF and change E4:E10 to required range

=SUMPRODUCT(--('Score Calc'!D4:D10="Corporate Marketing"),--('Score
Calc'!E4:E10="Critical"),('Score Calc'!CJ4:CJ10))


"paula k" wrote:

> Here is an example of my data:
>
>
> Org criticality
> score
> Corporate Marketing critical 100
> Human Resources critical 100
> Corporate Security non-critical 75
> Finance non-critical 100
> Corporate Marketing critical 50
>
> I need to search column A for a specific org & B for "critical" and sum
> their values column C.
>
> Example lookup corporate marketing & critical would add 100+50 = 150
>
>
> here is the formula I'm using that
> is close:
>
> =SUMPRODUCT(SUMIF('Score Calc'!D4:D10,{"Corporate Marketing"},'Score
> Calc'!CJ4:CJ10))
>
> I just need to know how to add another part to the array to also look up
> "Critical"
>
>