# countif multiple criteria but ignore duplicates

#### SKooLZ

##### New Member
Hi Guys, hope you can help me with a countif 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 running days must equals 3 in the example table below.

 Activity Date Total Running Days ? Running Track 08/06/2015 Running Track 08/06/2015 Running Fell 09/06/2015 Swimming 01/06/2015 Hockey 02/02/2015 Swimming 03/03/2015 Volleyball 04/03/2015 Running Track 04/04/2015 Running Track 04/04/2015

<tbody>
</tbody>

Also, is this possible without using an array formula?

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
Are you wanting to count the number of distinct days for the 'running track' activity?

Last edited:

Yes, distinct days for the cells containing the word 'Running'

Many Thanks..

Yes, distinct days for the cells containing the word 'Running'

Many Thanks..

 Row\Col A​ B​ C​ D​ 1​ Activity Date # distinct days: 2​ Running Track 8/6/2015​ running 3​ Running Track 8/6/2015​ 3​ 4​ Running Fell 9/6/2015​ 5​ Swimming 1/6/2015​ 6​ Hockey 2/2/2015​ 7​ Swimming 3/3/2015​ 8​ Volleyball 4/3/2015​ 9​ Running Track 4/4/2015​ 10​ Running Track 4/4/2015​

D3, control+shift+enter (cse), not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(D2,\$A\$2:\$A\$10)),\$B\$2:\$B\$10),\$B\$2:\$B\$10),1))

By the way, no need to worry about an array-processing formula, requiring cse.

Replies
0
Views
112
Replies
2
Views
347
Replies
0
Views
49
Replies
2
Views
79
Replies
12
Views
381

1,207,281
Messages
6,077,511
Members
446,287
Latest member
tjverdugo85

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