Sum with criteria

[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 unique dates in that year without counting the same date twice and also only counting dates where col B >0.

Col A Col B

01/01/2018 1
01/01/2018 0
02/01/2018 1
02/01/2018 2
02/01/2018 0
03/01/2018 0
01/05/2016 1
03/05/2016 2

So if I were to search the above for unique days in 2018, with dates where col B is >0, the answer would be 2.

Any help appreciated, thank you.

 

[dmheller]

try something like sumifs
=sumifs(B;B,A:A,">="&"select a date with the year you want",A:A,"<="&eomonth(select date you want,0))

 

[dmheller]

sorry I did it for a month you want a date
do
sumifs(B:B,A:A,">"&select a date that is one day less than what you want,A:A,"<"&select a day greater tan what you want
you might need to put 2 dates I cells

 

[Tikey]

I have tried editing your formula to =sumifs(B;B,A:A,">="&"01/01/2018",A:A,"<="&31/12/2018,0)) without success. I am wishing to search for a year and count the unique dates within the year where the other column has a value >0.

Apologies if you have had several responses I am having problems replying.

 

[MARZIOTULLIO]

Hi

D1=SUM(IF(FREQUENCY(IF(YEAR(A1:A100)=2018,IF(B1:B100>0,IF(A1:A100<>"",A1:A100))),A1:A100),1)) control+shift +enter

 

[Tikey]

That works great, thanks very much. Top man.