# Counting unique values within a date range

#### Luke.Speam

##### New Member
Hi,
I have been using a spreadsheet (Excel 2008 for Mac, OS X 10.6) to track how much I have spent using my debit card for some months now. One particular piece of information I would like to get from my spreadsheet is how many times I have been shopping each month. by 'been shopping', I mean on how many days have I spent money (occasionally I will go to two or three shops on the same day).
Thus, I have been trying to find a formula to count the number of days I have been shopping in a give month. For example:

A B
1 10/10/11 £x
2 15/10/11 £x
3 15/10/11 £x
4 25/10/11 £x
5 25/10/11 £x
6 30/10/11 £x
7 1/11/11 £x
8 5/11/11 £x
9 5/11/11 £x
10 12/11/11 £x
11 12/11/11 £x
12 12/11/11 £x
13 20/11/11 £x
14 25/11/11 £x

The £ values in column B don't matter. I would like to find some formula that can take the dates in A1:A14 and tell me that during the month of October I went shopping on 4 days, and during the month of November I went shopping on 5 days. Obviously, separate formulae for each month are necessary.

So far I have been able to count either the number of non-unique shopping trips (6 in October, 8 in November) or the total unique shopping trips, regardless of month (9 in total).

Any help would be appreciated, thank you.

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
This may work:

=SUM(IF(FREQUENCY(B:B,B:B)>0,1))

Cheers

Soggy

Hi,
The following must work:
Let's say you have your data in columns A to C, when column B has the dates.
Now, in D1 write:
=IF(COUNTIF(B1:B1000,B1)=1,1,"")
and drag it to the end of data (I made it working up to line 1000, but you may change it).
In E1 enter:
=SUM(D:D)
Now in E1 you have a total number of unique shopping days. You may separate it per month, per year - up to you.

Does this help?

This may work:

=SUM(IF(FREQUENCY(B:B,B:B)>0,1))

Cheers

Soggy

That formula is a much more efficient way of calculating total unique days shopping than my current one, so thanks. However, it does not take into account the date range, which is most important for me.

@alexf972
Thanks for the suggestion, I'll be sure to try that out. However, to do so I'd have to do a fair bit of reorganising of my data (i.e. adding in a few columns and replicating this), which I would prefer to avoid. Is there possibly a way of doing what you described in only a single formula, evading the need to create the new column?

Thanks anyway.

Probably like this.
Excel Workbook
ABCDEFG
2110-10-2011x10106
3215-10-2011x10118
4315-10-2011x10
5425-10-2011x10
6525-10-2011x10
7630-10-2011x10
871-11-2011x11
985-11-2011x11
1095-11-2011x11
111012-11-2011x11
121112-11-2011x11
131212-11-2011x11
141320-11-2011x11
151425-11-2011x11
Cell Formulas
RangeFormula
G2=COUNTIF(\$D\$2:\$D\$15,F2)
G3=COUNTIF(\$D\$2:\$D\$15,F3)
D2=MONTH(B2)
D3=MONTH(B3)
D4=MONTH(B4)
D5=MONTH(B5)
D6=MONTH(B6)
D7=MONTH(B7)
D8=MONTH(B8)
D9=MONTH(B9)
D10=MONTH(B10)
D11=MONTH(B11)
D12=MONTH(B12)
D13=MONTH(B13)
D14=MONTH(B14)
D15=MONTH(B15)

Thanks for the suggestion, Oeldere. I suppose that is one way of doing it (I'd need to do a few IF formulae though, as I'm also counting the number of days shopping within time periods other than months, i.e. term/semester dates at university). But, that doesn't take into account duplicates.
Like with alexf972's suggestion, I'd prefer not to rely on adding new columns or reorganising information. But thanks a lot for the help in any case.

In that case use: i.e.

=SUMPRODUCT((\$B\$2:\$B\$15>=B2)*(\$B\$2:\$B\$15<=B7))

That doesn't exclude duplicates.
Is there any way I could modify that formula, as it is nice and simply, to exclude duplicates?
Ideally it would be something like:
=SUMPRODUCT((\$B\$2:\$B\$15>=01/10/11)*(\$B\$2:\$B\$15<1/11/11))
with some sort of exclusion of duplicates function included.

I suppose you need a helpcollumn in that case, and that is not what you want.

You didn't mention duplicates in your question, so i didn't figured that out.

I guess I'll get to work creating some new columns then.
I did mention that I wanted to count the number of days I had been shopping, but I guess that was not as clear as I had hoped.
Thanks for the help anyway, and I am still welcoming any further suggestions.

Replies
9
Views
354
Replies
1
Views
154
Replies
7
Views
221
Replies
4
Views
187
Replies
4
Views
82

1,203,524
Messages
6,055,904
Members
444,832
Latest member
bgunnett8

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