Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a spreadsheet with a number of row records containing country/value pairs (A2:C11) and row records representing regions listing those countries horizontally (14:16):

enter image description here

What I would like to do is to sum up all the country values for each region:

enter image description here

Conceptually I know what I need to do, for instance for North America (B19):

  • filter B2:B11 range where B found in row 14
  • sum up values from C2:11 corresponding to filtered B2:B11 range

Which in this case would give 10 (C2) + 11 (C5) + 12 (C10) = 33

I believe the functions I need are the lookup and reference functions, but I just have no idea as to where to start:

Can someone give me an example for B19 (or at least list of functions I should be using)?

PS: I don't want to use VBA, just formulas

share|improve this question

migrated from stackoverflow.com Jul 1 '13 at 13:04

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 4 down vote accepted

Try this formula in B19 copied down

=SUMPRODUCT(SUMIF(B$2:B$11,INDEX(B$14:C$16,MATCH(A19,A$14:A$16,0),0),C$2:C$11))

  • MATCH gives you the correct row in table two
  • then INDEX gives you all countries in that row.
  • The INDEX/MATCH part then forms the criteria argument for SUMIF, and because the criteria is a range of values SUMIF returns an array (the sum for each country separately)......so SUMPRODUCT is used to sum the array, avoiding "array entry"
share|improve this answer
1  
It worked on B19 but I had to replace MATCH(A$19 with MATCH($A19 to copy it down to B20 and B21. –  user359650 Jul 1 '13 at 8:13
    
Sorry, yes - that $ shouldn't have been included........now corrected.... –  barry houdini Jul 1 '13 at 8:34
    
AFAIK there isn't an equivalent to SUMPRODUCT for counting, am I right? –  user359650 Jul 1 '13 at 9:33
1  
If you just want a count replace SUMIF with COUNTIF, SUMPRODUCT would remain, so =SUMPRODUCT(COUNTIF(B$2:B$11,INDEX(B$14:C$16,MATCH(A19,A$14:A$16,0),0))) –  barry houdini Jul 1 '13 at 9:55
    
You're the best ;) –  user359650 Jul 1 '13 at 10:12

Okay, since there's more than 1 country per region, you'll need a rather lengthy formula. I'm suggesting using both SUMIF() and VLOOKUP().

=SUMIF($B$2:$B$11,VLOOKUP(A19,$A$14:$C$16,2,FALSE),$C$2:$C$11)+
 SUMIF($B$2:$B$11,VLOOKUP(A19,$A$14:$C$16,3,FALSE),$C$2:$C$11)

The first SUMIF() is calculating the sum for Canada only, and the second for US only. Adding the two gives the sum for the region.

VLOOKUP() retrieves the country name from the region name, you'll notice the different is in the VLOOKUP(), where the first is 2 and the second is 3 (just before false). You can of course replace FALSE by 0 to output the same results.


Also, if you want a shorter, but an array formula, this one works as well:

=SUM(SUMIF($B$2:$B$11,IF(A19=$A$14:$A$16,$B$14:$C$16),$C$2:$C$11))

Since this is an array formula however, you'll have to use Ctrl+Shift+Enter

share|improve this answer
    
First formula works, but not the second. –  user359650 Jul 1 '13 at 8:15
    
@user359650 You have to press Ctrl, Shift AND Enter at the same time for it to work instead of simply Enter here as it is an array formula :) –  Jerry Jul 1 '13 at 8:17
    
What I like with your solution is that I can easily replace SUMIF with COUNTIF if I want to count, whereas with Barry's solution I don't have an equivalent for SUMPRODUCT –  user359650 Jul 1 '13 at 9:25
    
+1 for the array formula - that would definitely be the way to go where you have multiple matches for the region....... –  barry houdini Jul 1 '13 at 9:59

You can try this:

=SUMPRODUCT($C$2:$C$11,--(IFERROR(MATCH($B$2:$B$11,$B14:$C14,0),0)<>0))

at cell B19 and drag it down. If you have more than 2 countries per region, you need to extend the $B14:$C14 accordingly.

Note: this is an array formula, so you copy it to the cell and press Ctrl+Shift+Enter to make it work.

  • IFERROR(MATCH(...,0)) returns an array whose elements show the position of each entry of the first table, in the appropriate row of the second table.
  • The logical condition converts all zeros to FALSE and all other numbers to TRUE
  • The -- part converts the TRUE and FALSE values to 1 and 0 respectively
  • SUMPRODUCT operates on two arrays: the first is the original range, and the second is a 0/1 array that encompasses the conditions we entered before.

Here is a nice post that explains the logic behind such formulas: http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html

share|improve this answer
1  
This works without referencing the region so it requires regions in tables 2 and 3 to be listed in the same order - you could simplify to this version if that was the case =SUMPRODUCT(C$2:C$11*(B$2:B$11=B14:C14)) –  barry houdini Jul 1 '13 at 10:30
    
@barryhoudini Yes, I assumed that there is no need to check that tables are in same order. The above method is still nice (and your version much more condensed), but I would still go with your original answer as it avoids array formulas. –  Ioannis Jul 1 '13 at 10:42
    
@barryhoudini Just checked =SUM(C$2:C$11*(B$2:B$11=B14:C14)) (instead of SUMPRODUCT as above). It still works, since finally there is only one array (the Cartesian product of the conditions). Nice! –  Ioannis Jul 1 '13 at 10:50
    
Yes, SUM is good too, I only suggest SUMPRODUCT because it doesn't need to be "array entered" as it would with SUM –  barry houdini Jul 1 '13 at 10:53
    
@barryhoudini V Good point - Thanks! –  Ioannis Jul 1 '13 at 10:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.