Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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" |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum of multiple arrays?
=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" |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum of multiple arrays?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum of multiple arrays?
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Arrays, Vlookup | Excel Worksheet Functions | |||
multiple arrays for SMALL command | Excel Worksheet Functions | |||
Creating a single vertical array from multiple column arrays | Excel Worksheet Functions | |||
How do i auto create multiple files from 1 with multiple sheets | Excel Worksheet Functions | |||
averaging specific rows in multiple arrays | Excel Worksheet Functions |