Using SumIfs for date ranges

[robyoung81]

Hi all,

New to the forum, please be gentle....

I'm desperately trying to work out this formula and not having much luck.

Basically i have a list of product numbers, with the amount of units sold per day, with the dates being listed in Row 1 (From Column C to AU) and the product number in Column A, and the units in the corresponding cells.

See below for example:

A B C D E F
01/01/11 02/01/11 03/01/11 04/01/11
1 123 Description 5 7 9 4
2 124 Description 6 4 3 1
3 125 Description 8 12 19 20
4 126 Description 15 7 1 8

Then in another sheet i have the list of Product numbers and the date ranges....

A B C D

1 123 01/01/11 03/01/11
2 125 02/01/11 04/01/11
3 126 01/01/11 04/01/11
4 124 03/01/11 04/01/11

... and i want column D to return the amount of units sold for that product number within those date ranges.

So in the example above, column D should look like...

21
51
31
4

Does all this make sense?

So my only questions are... is this possible? And if so, how...?

Thanks very much for your time, really hope you can help and let me know if you need any more information from me.

Regards Rob

 

[njimack]

Welcome to the board. Something like this...

Excel Workbook
	A	B	C	D
2	123	01-Jan	03-Jan	21
3	124	02-Jan	04-Jan	51
4	125	01-Jan	04-Jan	0
5	126	03-Jan	04-Jan	0
Sheet2

Excel Workbook
	A	B	C	D	E
1	*	01-Jan	02-Jan	03-Jan	04-Jan
2	123	5	7	9	4
3	124	6	4	3	1
4	125	8	12	19	20
5	126	15	7	1	8
Sheet1

Also, consider using dynamic ranges - http://www.contextures.com/xlNames01.html#Dynamic

 

[robyoung81]

Thanks very much, it works perfectly when i copy your formula (and sheets ) in to a blank Spreadsheet.

But when i change the formula to the correct Sheet names and cell ranges for my spreadsheet i get an #N/A error!

This is my formula... where 'LY Gross Units' is Sheet 1 and 'Promos' is Sheet 2'.

{=SUM(IF('LY Gross Units'!$A$2:$A$979=Promos!G2,IF('LY Gross Units'!$B$1:$GA$1>=Promos!$E2,IF('LY Gross Units'!$B$1:$GA$1<=Promos!$F2,'LY Gross Units'!$B$2:$GA$980))))}

What am i doing wrong? I've checked all the formatting of the numbers and dates and they seem to correct/consistent.

Thanks again!

rob

 

[njimack]

Your 2 vertical ranges need to be the same size - one goes to row 979, the other to row 980.