Match/Index/Sum function with date range

[JonathanK1]

Hi all,

I just joined this site today. It's nice to "meet" everyone. I've bee
racking my brains all day over this formula. I would really appreciat
it if someone could help me.

So, I have a worksheet with products down one axis and dates across th
other (dates up top). The data in the middle is the number of product
for each given day. What I want to do in the next tab/worksheet is pic
the product and the date range, and have the added number show up. Fo
instance, Product A from April 1st to April 25nd equals 25 (because
chose product A and because there were 1 of them each day). Does thi
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:NC1,0)),(INDEX(TOTALS!B1:NC1,0,MATCH(I11,TOTALS!A2:A90,0)))))

Its adding something, but it's not adding it right (if that's even wha
its doing). The first index in the formula above is the FROM and th
next is the TO - the last is the product choice. I think I'm way of
here.

Thanks

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Spencer101]

Hi all,

I just joined this site today. It's nice to "meet" everyone. I've bee
racking my brains all day over this formula. I would really appreciat
it if someone could help me.

So, I have a worksheet with products down one axis and dates across th
other (dates up top). The data in the middle is the number of product
for each given day. What I want to do in the next tab/worksheet is pic
the product and the date range, and have the added number show up. Fo
instance, Product A from April 1st to April 25nd equals 25 (because
chose product A and because there were 1 of them each day). Does thi
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:NC1,0)),(INDEX(TOTALS!B1:NC1,0,MATCH(I11,TOTALS!A2:A90,0)))))

Its adding something, but it's not adding it right (if that's even wha
its doing). The first index in the formula above is the FROM and th
next is the TO - the last is the product choice. I think I'm way of
here.

Thanks!

Hi,

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

+-------------------------------------------------------------------
|Filename: JonathanK 1 Example.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=702
+-------------------------------------------------------------------

 

[joeu2004]

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.