countif multiple criteria but ignore duplicates

[SKooLZ]

Hi kind excel community...I'm after your precious time to help me with formula to count the days the running activity was done and ignore the amount of times it might have been done in the same day. The count for distinct running days containing the word "Running" must equals 3 in the attached example table.

Also, is this possible without using an array formula?

Many many thanks in advance...

[AlKey]

Try this formula

=SUMPRODUCT((ISNUMBER(SEARCH("Running",A3:A11))/COUNTIF(B3:B11,B3:B11)))

[FDibbins]

I used a helper, and copied this down...
=IF(COUNTIFS($A$3:A3,"run*",$B$3:B3,B3)=1,1,"")

You can then just count the helper

[Ron Coderre]

Using your posted workbook....
and
B1: an activity to match....running

this formula returns the unique dates containing that activity (Durable against blank cells)

Please Login or Register  to view this content.

Is that something you can work with?

[SKooLZ]

Thanks everyone for all your quick responses... you have helped me a great deal. Just what I asked for.

Try this formula

=SUMPRODUCT((ISNUMBER(SEARCH("Running",A3:A11))/COUNTIF(B3:B11,B3:B11)))

Alkey , your formula is correct but gave a blank error due to some blanks but I preferred you { search("Running" } bit of the formula which I added to Ron Coderre's formula below (which is the same as yours but blank proof hence it worked) to avoid referencing a cell.

B1: an activity to match....running

this formula returns the unique dates containing that activity (Durable against blank cells)
Select Code copy to clipboard
C1: =SUMPRODUCT(ISNUMBER(SEARCH(B1,A4:A22))/COUNTIF(B4:B22,B4:B22&""))
Is that something you can work with?

FDibbins, thanks for your input but I just need the formula in one fixed cell and my worksheet cannot accommodate the helper method. But thanks.

So Both Alkey and Ron Coderre get kudos on this one... Rep added!!

[SKooLZ]

oh, and one more thing.... how do I add an additional condition of say year, probably by referencing a different cell.
So how many days running activity happened in 2014?

Thanks in advance..

[Ron Coderre]

Using your posted workbook
and
A1: a year....2015
B1: an activity....Running

Edited to replace a faulty formula with a correct one

This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER) returns the count of unique dates that satisfy those requirements:

Please Login or Register  to view this content.

Does that help?

[SKooLZ]

Using your posted workbook
and
A1: a year....2015
B1: an activity....Running

Edited to replace a faulty formula with a correct one

This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER) returns the count of unique dates that satisfy those requirements:

Please Login or Register  to view this content.

Does that help?

Thanks Ron, work but as it's an array formula it's dramatically slowed down my workbook because my file look range is cells A3:A1048576... dynamic name ranges are not an option as the worksheet gets copied frequently which will creates multiple named ranges when that happens.

is there any slight chance this is achievable without an array like your previous formula??

[FDibbins]

FDibbins, thanks for your input but I just need the formula in one fixed cell and my worksheet cannot accommodate the helper method.

unlikely, but OK

Thanks Ron, work but as it's an array formula it's dramatically slowed down my workbook because my file look range is cells A3:A1048576

So reduce the range to just what you need - or at worst, 2-3 times what you think you would need