Sumproduct ?

[Tikey]

Hi

I have a column of dates which cover several years in the format 01/01/2018, this column is modified with dates added to and removed regularly. I wish to be able to search for a particular year and then sum all the dates in that year without counting the same date twice.

I have been trying SUMPRODUCT without any success, can anyone offer any guidance please.

Thanks.

 

[James006]

Hi,

Your explanation is not totally clear ... :wink:

Do you actually to sum 'dates' or some amounts for these dates ....?

Why don't you post your sumproduct formula ... it could be a simple issue to fix ...

 

[Tikey]

Sorry about that, I wish to sum the number of these dates eg

01/01/2018
01/01/2018
02/01/2018
02/01/2018

The answer would be 2. I haven't included my attempt as it appeared to have too many errors.

 

[James006]

Hello again,

So ... your objective is to Count the Unique dates ... :wink:

Say all your dates are located in cells A1 to A100 ... you could have your total count in cell C1 with following formula:

[FONT=arial]=SUMPRODUCT(1/[/FONT]COUNTIF(A1:A100,A1:A100))

Hope this will help

 

[Tikey]

Thanks for your reply.

Whilst I'm sure your suggestion will count the unique dates, I don't believe it will deal with the other requirements. It needs to be able to search through the column of dates for those dates which are in a specified year and then count the dates, the number of dates within the column also changes regularly and therefore I am assuming the cell range cannot be specified but will need to use a full column address ie A:A

 

[James006]

Re,

If you need to restrict the year ... you could test the following array formula :

=SUM(--(FREQUENCY(IF(Year(A1:A100)=2018,A1:A100),A1:A100)>0))

You have to use simultaneously the 3 keys : Control Shift Enter ... instead of the standard Enter key ...

HTH

 

[Tikey]

Thank you.

That works fine with the range of cells, when I modify that to a full column it still works fine, but if I modify it to a full column on another sheet I get a #VALUE ! error.

 

[James006]

Glad you are getting closer ... to your solution ...

The danger of a ' full column ' ... is that you might have text such as headers which will disrupt the formula ...

 

[Tikey]

Hi

I've had a further play with this and it works ok on a range of cells on another sheet, but not the full column, could the fact that I have a header to the column be causing the problem ?

 

[James006]

Hi

I've had a further play with this and it works ok on a range of cells on another sheet, but not the full column, could the fact that I have a header to the column be causing the problem ?

Yes ...

Did you read my message # 8 ...? :wink: