Using SumIfs for date ranges

robyoung81

New Member
Joined
Mar 8, 2011
Messages
2
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? :eek:

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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

robyoung81

New Member
Joined
Mar 8, 2011
Messages
2
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Your 2 vertical ranges need to be the same size - one goes to row 979, the other to row 980.
 

Forum statistics

Threads
1,144,392
Messages
5,724,081
Members
422,536
Latest member
Zeeshan53

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top