Counting unique values within a date range

[Luke.Speam]

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.

 

[Soggy]

This may work:

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

Cheers

Soggy

 

[alexf972]

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?

 

[Luke.Speam]

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.

 

[Oeldere]

Probably like this.

Excel Workbook
	A	B	C	D	E	F	G
2	1	10-10-2011	x	10		10	6
3	2	15-10-2011	x	10		11	8
4	3	15-10-2011	x	10
5	4	25-10-2011	x	10
6	5	25-10-2011	x	10
7	6	30-10-2011	x	10
8	7	1-11-2011	x	11
9	8	5-11-2011	x	11
10	9	5-11-2011	x	11
11	10	12-11-2011	x	11
12	11	12-11-2011	x	11
13	12	12-11-2011	x	11
14	13	20-11-2011	x	11
15	14	25-11-2011	x	11
Blad1

Cell Formulas
Range	Formula
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)

 

[Luke.Speam]

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.

 

[Oeldere]

In that case use: i.e.

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

 

[Luke.Speam]

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.

 

[Oeldere]

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.

 

[Luke.Speam]

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.