Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Match/Index/Sum function with date range
Hi all,
I just joined this site today. It's nice to "meet" everyone. I've been racking my brains all day over this formula. I would really appreciate it if someone could help me. So, I have a worksheet with products down one axis and dates across the other (dates up top). The data in the middle is the number of products for each given day. What I want to do in the next tab/worksheet is pick the product and the date range, and have the added number show up. For instance, Product A from April 1st to April 25nd equals 25 (because I chose product A and because there were 1 of them each day). Does this make sense? I hope so. So here is what I have so far... =SUM(INDEX(TOTALS!B2:NC90,0,MATCH(I7,TOTALS!B1:NC1 ,0)),(INDEX(TOTALS!B2:NC90,0,MATCH(I9,TOTALS!B1:NC 1,0)),(INDEX(TOTALS!B1:NC1,0,MATCH(I11,TOTALS!A2:A 90,0))))) Its adding something, but it's not adding it right (if that's even what its doing). The first index in the formula above is the FROM and the next is the TO - the last is the product choice. I think I'm way off here. Thanks! |
#2
|
|||
|
|||
Quote:
Have a look at the attached. Is this what you mean? It's one way of doing it at least... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match/Index/Sum function with date range
"JonathanK1" wrote:
So, I have a worksheet with products down one axis and dates across the other (dates up top). The data in the middle is the number of products for each given day. What I want to do in the next tab/worksheet is pick the product and the date range, and have the added number show up. For instance, Product A from April 1st to April 25nd equals 25 (because I chose product A and because there were 1 of them each day). [....] So here is what I have so far... =SUM(INDEX(TOTALS!B2:NC90,0,MATCH(I7,TOTALS!B1:NC1 ,0)), (INDEX(TOTALS!B2:NC90,0,MATCH(I9,TOTALS!B1:NC1,0)) , (INDEX(TOTALS!B1:NC1,0,MATCH(I11,TOTALS!A2:A90,0)) ))) [....] The first index in the formula above is the FROM and the next is the TO - the last is the product choice. =SUMPRODUCT((TOTALS!A2:A90=I11)*(I7<=TOTALS!B1:NC1 )*(TOTALS!B1:NC1<=I9),TOTALS!B2:NC90) You might want to use addresses of the form TOTALS!$A$2:$A$90, but leaving I11, I7 and I9 as is if you want to be able to copy the formula across several columns. For example, if J7, J9 and J11 represent another date/product combination; K7, K9 and K11 represent a third; etc. |
#4
|
|||
|
|||
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX and MATCH with range | Excel Worksheet Functions | |||
Index / Match / value in a range? | Excel Discussion (Misc queries) | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Index-Match from a range | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |