Excel - summarize one table with multiple criteria in two

Question

      A              B       C
1 Report Line   Data Type   Value  
2     1             a           10
3     1             b           20
4     1             c           30
5     2             a           40
6     2             b           50
7     2             c           60
8     3             a           70
9     3             b           80
10    3             c           90


     E           F
1 Report    Report Line
2      1          1
3      1          3
4      2          2
5      2          3

Hi,

in the above example, the first table shows report lines of which each can belong to one or more reports. The second table shows which report line belongs to which reports.

How do I summarize all rows in column C where data type is "a" for the lines that belong to report 2 in the table given in E:F? This should be 40+70=110.

I would like to do it without referencing any criteria ranges outside these two tables, one formula in one cell. Tried searching everything I could think of in match, index, sumifs, sumproduct...

Many thanks.

Can you index your second table to your first? i.e. add a column that says 'report', but for each record? Then it would be easy. — DaveRGP
– DaveRGP, Commented Jan 14, 2015 at 12:20

Community · Accepted Answer · 2017-03-20 10:18:14Z

1

you can use following array formula (entered by CTRL+SHIFT+ENTER):

=SUMPRODUCT(
    C2:C10,
    --(B2:B10="a"),
    --(ISNUMBER(MATCH(A2:A10,IF(E2:E5=2,F2:F5),0))))

Explanation (see also Excel vocabulary to find solutions faster):

IF(E2:E5=2,F2:F5) inside an array formula will check each cell from E2 to E5 and if it is equal to 2, it will append corresponding value from F-column, if not it will append FALSE to an output array
MATCH(A2:A10, inner_array, 0) is taking the inner_array as a normal argument, but because 1st argument is supposed to be only 1 value, inside an array formula it will check each cell from A2 to A10 for an exact match agains the provided inner_array, if a match is found it appends the position inside the inner_array, if not it appends #N/A! to an output array
ISNUMBER(array) when used in an array formula will check each element in provided array, it appends TRUE or FALSE to an output array
--(boolean_array) will convert each item in the array from boolean value to 0 or 1
SUMPRODUCT(range, array, array) will multiply the numbers at each corresponding position in provided ranges or arrays, then it will sum up all the results

edited Mar 20, 2017 at 10:18

CommunityBot

11 silver badge

answered Jan 14, 2015 at 12:57

Aprillion

22.4k6 gold badges59 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gorkas Over a year ago

Thank you, this is impressive, I hope I will one day understand this formula. Also thanks for the vocabulary post, very useful.

Aprillion Over a year ago

@gorkas I added an explanation, hope it helps and please don't forget to accept the answer if it solved your problem :)

Collectives™ on Stack Overflow

Excel - summarize one table with multiple criteria in two

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related