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!

Hi,

Have a look at the attached. Is this what you mean?
It's one way of doing it at least...

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.

You guys are awesome...that totally worked. I can't thank you enough!