Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Member
 
Posts: 40
Default 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   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by JonathanK1 View Post
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...
Attached Files
File Type: zip JonathanK 1 Example.zip (1.8 KB, 85 views)
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 829
Default 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   Report Post  
Member
 
Posts: 40
Default

Quote:
Originally Posted by joeu2004[_2_] View Post
"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!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
INDEX and MATCH with range oliverbradley Excel Worksheet Functions 3 March 30th 10 06:44 PM
Index / Match / value in a range? Andrew Excel Discussion (Misc queries) 2 March 29th 10 02:01 AM
index match array function-returning only first match, need last. Julie Olsen Excel Worksheet Functions 3 December 29th 06 01:50 AM
Index-Match from a range Mparekh Excel Worksheet Functions 2 April 4th 06 04:31 PM
Emulate Index/Match combo function w/ VBA custom function Spencer Hutton Excel Worksheet Functions 2 May 2nd 05 05:26 PM


All times are GMT +1. The time now is 11:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"