Help with conditional COUNTIF statement (date ranges)...

roseysdaddy

New Member
Joined
Aug 31, 2006
Messages
2
Ok, basically what I have are two columns. One column lists a store's name that sold a can of soup. The other column lists the date that the store sold it.
What I have now is a COUNTIF statement that reads "=COUNTIF(F6:F41,"Store #1")” that totals up the number of times Store #1 sold a can of soup.

Below that however, I would like to have it broken down by week so I can further tell how many times Store #1 from 7/1/06 through 7/6/06 and below that 7/7/06-7/13/06 and so forth. Can anyone explain how to set this up or point me towards an example?

Thanks!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi,roseysdaddy

Welcome to the board!

Suppose your date list in column G:

Try the formula below:
Code:
=Sumproduct((F6:F41="Store #1")*(G6:G41>="7/1/06")*(G6:G41<="7/6/06"))

Best Regards
 
Upvote 0
roseysdaddy,
A Pivot Table would work very well with this, for example:
Book1
ABCDEF
1StoreDateCountofDate
211-JulStoreDateTotal
312-Jul17/1/2006-7/6/20064
416-Jul1Total4
521-Jul27/1/2006-7/6/20062
626-Jul7/7/2006-7/12/20061
727-Jul7/13/2006-7/14/20061
8213-Jul2Total4
916-JulGrandTotal8
Sheet1
 
Upvote 0
Or to expand on northwolves' solution, so you don't have to edit formulas:
Book1
ABCDEFG
1StoreDateInputStoreInputStartDateInputEndDate#ofSales
211-Jul11-Jul6-Jul4
312-Jul
416-Jul
521-Jul
626-Jul
727-Jul
8213-Jul
916-Jul
Sheet1
 
Upvote 0
great, thanks for the replies. how do i set this up when the stores are called something like "Jackson" and "Huntington" or other city names like "Huntington 4" (and i cant even see what the forumla is for the first reply....am i missing something?) ??

again, thanks.
 
Upvote 0
Hi,roseysdaddy

Welcome to the board!

Suppose your date list in column G:

Try the formula below:
Code:
=Sumproduct((F6:F41="Store #1")*(G6:G41>="7/1/06")*(G6:G41<="7/6/06"))

Best Regards

You'll need to coerce the dates

=SUMPRODUCT((F6:F41="Store 1")*(G6:G41>="7/1/06"+0)*(G6:G41<="7/6/06"+0))
 
Upvote 0
great, thanks for the replies. how do i set this up when the stores are called something like "Jackson" and "Huntington" or other city names like "Huntington 4" (and i cant even see what the forumla is for the first reply....am i missing something?) ??

again, thanks.
There are no formulas to see for the pivot table. It's a feature in Excel from the Data menu. If you want to use my 2nd solution similar to northwolve and Brian from Maui and you have store names; Replace the store #s with the names, then input the store name in the Input Store field(cell D2 in my example)
 
Upvote 0

Forum statistics

Threads
1,223,431
Messages
6,172,073
Members
452,444
Latest member
ShaImran193

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
Back
Top