I have two sets of ranges in two different tabs.
One has A through D with values going down. The other range has A through D (in different order) with the Country of Origin. I forgot to add, the order of the excel file is incorrect. The Country of Origin column is in B and A through D is in C. I cannot change it as it is locked from another party.
I want to get the sum of the values based on Country of Origin using sumifs. I tried offset and Vlookup, but had no luck.
Last edited by kakaman; 10-08-2015 at 04:21 PM.
I don't see where you get 12 and 14 from, US = 13, Canada = 13.
Formula:
=SUM(SUMIF($B$7:$B$10,IF($C$2:$C$5=C13,$B$2:$B$5),$C$7:$C$10))
*** Must be confirmed as an Array Formula by using Shift Ctrl and Enter, not just Enter ***
If you get 0 results then this has not been done correctly.
I tried copy and pasting the formula but I keep getting 0, even as an array formula. Do you mind attaching an excel file? I also have Excel 2010, not sure if that makes a difference.Originally Posted by jason.b75
I don't see where you get 12 and 14 from, US = 13, Canada = 13.
Formula:=SUM(SUMIF($B$7:$B$10,IF($C$2:$C$5=C13,$B$2:$B$5),$C$7:$C$10))
*** Must be confirmed as an Array Formula by using Shift Ctrl and Enter, not just Enter ***
If you get 0 results then this has not been done correctly.
Here ya go, this is done with your sample file from post #1, with the formula in C14 and D14.
if your source file is big, I prefer to use helper column
1. A7=VLOOKUP(B7,$B$2:$C$5,2,FALSE), copy to A10
2. C20=SUMPRODUCT(($A7:$A10=C$13)*($C$7:$C$10)) , copy to D20
If you're going to use a helper to make it more efficient then might as well maximise the effect.
1. Index and Match is more efficient than Vlookup.
2. Sumif is more efficient than Sumproduct.
Formula:=INDEX($C$2:$C$5,MATCH(B7,$B$2:$B$5,0))
Formula:=SUMIF($A$7:$A$10,C$13,$C$7:$C$10)
They are about the same efficiency-wise but INDEX is more versatile.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
I did a timer test with the 2 a couple of years ago, index / match was about 20% faster to recalculate, much to my annoyance at the time, (there was a certain mvp that I wanted to prove wrong).
That said, it does need a significant amount of data for the difference to be noticeable.
In Excel 2002 I filled column A with the row numbers 1 to 65536.
In column B, B1:B65536 I entered random numbers.
I then tested the calculation times for these 2 formulas:
Formula 1: =VLOOKUP(50000,A:B,2,0)
Formula 2: =INDEX(B:B,MATCH(50000,A:A,0))
I did 5 calculations of each formula and averaged those results.
Data Range
Perfect, thanks everyone!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks