Is it possible to calculate the sum of a range of cells where the start and end of the range are determined by two VLOOKUP formulas? My thoughts were that it would look something like this:
=SUM(IFERROR(VLOOKUP(TEXT("Item Coupons",),'2290 1'!$A$1:$C$150,2,FALSE),"0") : (IFERROR(VLOOKUP(TEXT("Item Percents",),'2290 1'!$A$1:$C$150,2,FALSE),"0")))
Many thanks
Last edited by bwaite87; 12-13-2012 at 04:37 PM.
welcome to the forum.
this is probably do-able, but take a look at the sumif() and sumifS() functions, they may give you what you want.
failing that, i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
Last edited by FDibbins; 12-13-2012 at 06:03 PM.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Here's a sample.
This is an extremely simplified version of what I need to work with.
There are two sheets: 2290 1 and Sheet2.
Sheet 2 needs to pull data from 2290 1.
I need "item coupons" and "item percents" summed and placed in the cells in Sheet2.
Now I know i could simply use a vlookup for "PROMO" etc etc... but my problem is that I do this on a weekly basis and the 2290 1 sheet always changes. Different data with different names pull in every week.
So I thought i could use a vlookup formula for "item coupons" (B4) and another one for "item percents" (B9)and have everything in between summed.
The data pulls into different cells every week, the only constant is that what i need is always in the B column.
I hope this helps, thanks again!
did you put the subtotals in? or is that the number that you are looking for?
do you want the sum of the qty or the $ value?
based on your sample, this will give you...
the total qty
=SUM(OFFSET('2290 1'!$A$1,MATCH(Sheet2!A$1,'2290 1'!$A$1:$A$100,0),1,2))
total $
=SUM(OFFSET('2290 1'!$A$1,MATCH(Sheet2!A$1,'2290 1'!$A$1:$A$100,0),2,2))
both can be copied across to give item percent
edit: modified to allow for more rows btw coupons and percent...
=SUM(OFFSET('2290 1'!$A$1,MATCH(Sheet2!A$1,'2290 1'!$A$1:$A$100,0),ROW(A1),MATCH(Sheet2!$B$1,'2290 1'!$A$1:$A$100,0)-MATCH(Sheet2!$A$1,'2290 1'!$A$1:$A$100,0)-3))
this can be copied across for volume and dow 1 row for $ value
Last edited by FDibbins; 12-13-2012 at 08:33 PM.
That is phenominal, thank you!
The subtotals are put in automatically, and for dollar values yes that is the amount I'm trying to find.
I actually needed both, the quantity and the dollar value.
The second formula you put in worked like a charm, but I can't seem to figure out how to make it work for the dollar value, can you help?
Please see attached second sample, how would i make it work for that?
Sample2.xlsx
hi. i had actually put the $ amount below the qty amount, use this in C1...
=SUM(OFFSET('2290 1'!$A$1,MATCH(Sheet2!A$1,'2290 1'!$A$1:$A$100,0),ROW(A2),MATCH(Sheet2!$B$1,'2290 1'!$A$1:$A$100,0)-MATCH(Sheet2!$A$1,'2290 1'!$A$1:$A$100,0)-3))
copied to D1
Thanks again, you've made my day!
happy to help, and thanks for the rep
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks