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

##### New Member
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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

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

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

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

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.

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))

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)

Replies
3
Views
171
Replies
3
Views
218
Replies
3
Views
189
Replies
6
Views
385
Replies
1
Views
353

1,203,469
Messages
6,055,601
Members
444,802
Latest member
lolica12

### 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.

### Which adblocker are you using?

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

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