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:
Please Login or Register to view this content.
*** 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:Please Login or Register to view this content.
*** 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:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
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
I use Excel 2002 for this because I have a file created in Excel 2002 dedicated to exactly this subject.
It's not important to know that one formula takes x amount of time to calculate and another formula takes y amount of time to calculate but it's good to know that one formula is more efficient than the other.
I do this kind of testing extensively.
I'm starting to wonder what other factors can make significant differences. Other applications, background processes, etc.
It's interesting to note that your testing shows that the process times are very close together, where as mine have a much greater difference.
For the purpose of testing, I entered =ROW() into A1:A50000, then copy and paste values.
In B1, =VLOOKUP(A1,$A$1:$A$50000,1,0) for the first run, then replace that with =INDEX($A$1:$A$50000,MATCH(A1,$A$1:$A$50000,0)) for the second run.
Then I ran a loop for 10 cycles to force calculation and post the timings.
Sorting both sets of times slowest to fastest in order to compare closest possible values, index was 7% faster on the quickest cycle, but on the other 9 cycles the difference was less than 1%, 6 of those less than 0.01%.
What was more interesting was the difference between the fastest and slowest cycles of the same formula.
Given these variations, it is easy to see where tests can favour one method if not evaluated correctly.
Please Login or Register to view this content.
Perfect, thanks everyone!
Yeah, everyone would get different results based on those factors plus some.
I use the timer code found here:
http://msdn.microsoft.com/en-us/library/aa730921.aspx
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks