SUMPRODUCT or ARRAY Formula

[Cordury2]

Is it possible to count the number of occurrences in between a date range if you have a unique identifier?

I can use the SumProduct function to sum the date but I want to count the # of times a 'Store Number' appears in my data dump.

Any help would be appreciated.

Or just I just try an IF statement?

 

[Cordury2]

Below if the formula I am using and it works (suming the date between the dates) but I want to count specific stores and dates, not just dates.


=SUMPRODUCT(('Top 50 Data'!$A$2:$A$205>=Sheet1!F6)*('Top 50 Data'!$A$2:$A$205<=Sheet1!E6),'Top 50 Data'!$N$2:$N$205)

 

[SkipVought]

Hi,

you're close...
[tt]
=SUMPRODUCT(('Top 50 Data'!$A$2:$A$205>=Sheet1!F6)*('Top 50 Data'!$A$2:$A$205<=Sheet1!E6)*('Top 50 Data'!$N$2:$N$205))
[/tt]
assuming that column A is dates and column N contains 'Store Number'.

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[Cordury2]

Thanks Skip. Column N had just had the number 1 in it. And the formula was summing the amount of occurrences in between the dates regardless of store number (and still is). When I enter in the corresponding store numbers in column N, I am getting some very large numbers.

The set up of the (data) spreadsheet is as follows:

Column A: Date, Column C: Store Number and Column N had the number "1" in it. The problem is the same store number can appear multiple times with different dates.

 

[SkipVought]

duh! sorry...
[tt]
=SUMPRODUCT(('Top 50 Data'!$A$2:$A$205>=Sheet1!F6)*('Top 50 Data'!$A$2:$A$205<=Sheet1!E6)*('Top 50 Data'!$N$2:$N$205=A1))
[/tt]
assuming that the store number value is in A1

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]