COUNTIFS that will ignore eligible criteria if the date has already been counted

[bbqpringles]

I'm trying to use COUNTIFS to get this done, but maybe there is a better way?

I'm trying to count the number of dates in my table (WK1_ATT) that have an eligible code. Here's the kicker - I only want to count each date once.

The formula below shows all of the eligible codes, but it will count the date more than once if there is more than one eligible code used on that date.

SUM(COUNTIFS(WK1_ATT[[#All],[EMP NO]],[@[EMP NO]],WK1_ATT[[#All],[CODES]],{"ADL","AL","BL","BLC","CP","CSL","CTL","FMC","FMCL","FMEC","FMLA","FMPL","HOT","IOD","MLT","PD","PPL","W"}))

(NOTE: my formula says "CODES" for the "CODE" header because the forum window is marking it off as code and I had to change the name)

For example, I only want to count 31-Jan-2022 once, but it's getting counted twice because both CP and W are eligible codes.

Because of this my COUNTIFS is returning "6.00" when I need it to count "5.00" (RDO and CPOT are not eligible, but CP and W are):

[/CODE]

 

[bbqpringles]

I ended up figuring this out, but I'll leave the post up in case there is someone that can help me find a more efficient formula.

The formula uses a conditional statement that figures out the correct count for each date, but doesn't allow the count for each individual date to be higher than 1.

SUM
COUNTIF
IF the MIN DATE of the DATE RANGE for this EMP NO and any eligible CODES >1, then 1
IF NOT, then give sum of the MIN DATE of the DATE RAGNE for this EMP NO and any eligible CODES
+IF the MIN DATE +1 of the DATE range...
+IF the MIN DATE + 2 of the DATE range...
etc. until all dates in the pay week have a total that are summed together

Here's the formula that gave me what I needed:

IF(SUM(COUNTIFS(WK1_ATT[[#All],[ATT DATE]], MIN(WK1_ATT[[#All],[ATT DATE]]),WK1_ATT[[#All],[EMP NO]],[@[EMP NO]],WK1_ATT[[#All],[CODE]],{"ADL","AL","BL","BLC","CP","CSL","CTL","FMC","FMCL","FMEC","FMLA","FMPL","HOT","IOD","MLT","PD","PPL","W"}))>1,1,SUM(COUNTIFS(WK1_ATT[[#All],[ATT DATE]], MIN(WK1_ATT[[#All],[ATT DATE]]),WK1_ATT[[#All],[EMP NO]],[@[EMP NO]],WK1_ATT[[#All],[CODE]],{"ADL","AL","BL","BLC","CP","CSL","CTL","FMC","FMCL","FMEC","FMLA","FMPL","HOT","IOD","MLT","PD","PPL","W"})))+IF(SUM(COUNTIFS(WK1_ATT[[#All],[ATT DATE]], MIN(WK1_ATT[[#All],[ATT DATE]])+1,WK1_ATT[[#All],[EMP NO]],[@[EMP NO]],WK1_ATT[[#All],[CODE]],{"ADL","AL","BL","BLC","CP","CSL","CTL","FMC","FMCL","FMEC","FMLA","FMPL","HOT","IOD","MLT","PD","PPL","W"}))>1,1,SUM(COUNTIFS(WK1_ATT[[#All],[ATT DATE]], MIN(WK1_ATT[[#All],[ATT DATE]])+1,WK1_ATT[[#All],[EMP NO]],[@[EMP NO]],WK1_ATT[[#All],[CODE]],{"ADL","AL","BL","BLC","CP","CSL","CTL","FMC","FMCL","FMEC","FMLA","FMPL","HOT","IOD","MLT","PD","PPL","W"})))+ IF(SUM(COUNTIFS(WK1_ATT[[#All],[ATT DATE]], MIN(WK1_ATT[[#All],[ATT DATE]])+2,WK1_ATT[[#All],[EMP NO]],[@[EMP NO]],WK1_ATT[[#All],[CODE]],{"ADL","AL","BL","BLC","CP","CSL","CTL","FMC","FMCL","FMEC","FMLA","FMPL","HOT","IOD","MLT","PD","PPL","W"}))>1,1,SUM(COUNTIFS(WK1_ATT[[#All],[ATT DATE]], MIN(WK1_ATT[[#All],[ATT DATE]])+2,WK1_ATT[[#All],[EMP NO]],[@[EMP NO]],WK1_ATT[[#All],[CODE]],{"ADL","AL","BL","BLC","CP","CSL","CTL","FMC","FMCL","FMEC","FMLA","FMPL","HOT","IOD","MLT","PD","PPL","W"})))+IF(SUM(COUNTIFS(WK1_ATT[[#All],[ATT DATE]], MIN(WK1_ATT[[#All],[ATT DATE]])+3,WK1_ATT[[#All],[EMP NO]],[@[EMP NO]],WK1_ATT[[#All],[CODE]],{"ADL","AL","BL","BLC","CP","CSL","CTL","FMC","FMCL","FMEC","FMLA","FMPL","HOT","IOD","MLT","PD","PPL","W"}))>1,1,SUM(COUNTIFS(WK1_ATT[[#All],[ATT DATE]], MIN(WK1_ATT[[#All],[ATT DATE]])+3,WK1_ATT[[#All],[EMP NO]],[@[EMP NO]],WK1_ATT[[#All],[CODE]],{"ADL","AL","BL","BLC","CP","CSL","CTL","FMC","FMCL","FMEC","FMLA","FMPL","HOT","IOD","MLT","PD","PPL","W"})))+IF(SUM(COUNTIFS(WK1_ATT[[#All],[ATT DATE]], MIN(WK1_ATT[[#All],[ATT DATE]])+4,WK1_ATT[[#All],[EMP NO]],[@[EMP NO]],WK1_ATT[[#All],[CODE]],{"ADL","AL","BL","BLC","CP","CSL","CTL","FMC","FMCL","FMEC","FMLA","FMPL","HOT","IOD","MLT","PD","PPL","W"}))>1,1,SUM(COUNTIFS(WK1_ATT[[#All],[ATT DATE]], MIN(WK1_ATT[[#All],[ATT DATE]])+4,WK1_ATT[[#All],[EMP NO]],[@[EMP NO]],WK1_ATT[[#All],[CODE]],{"ADL","AL","BL","BLC","CP","CSL","CTL","FMC","FMCL","FMEC","FMLA","FMPL","HOT","IOD","MLT","PD","PPL","W"})))+IF(SUM(COUNTIFS(WK1_ATT[[#All],[ATT DATE]], MIN(WK1_ATT[[#All],[ATT DATE]])+5,WK1_ATT[[#All],[EMP NO]],[@[EMP NO]],WK1_ATT[[#All],[CODE]],{"ADL","AL","BL","BLC","CP","CSL","CTL","FMC","FMCL","FMEC","FMLA","FMPL","HOT","IOD","MLT","PD","PPL","W"}))>1,1,SUM(COUNTIFS(WK1_ATT[[#All],[ATT DATE]], MIN(WK1_ATT[[#All],[ATT DATE]])+5,WK1_ATT[[#All],[EMP NO]],[@[EMP NO]],WK1_ATT[[#All],[CODE]],{"ADL","AL","BL","BLC","CP","CSL","CTL","FMC","FMCL","FMEC","FMLA","FMPL","HOT","IOD","MLT","PD","PPL","W"})))+IF(SUM(COUNTIFS(WK1_ATT[[#All],[ATT DATE]], MIN(WK1_ATT[[#All],[ATT DATE]])+6,WK1_ATT[[#All],[EMP NO]],[@[EMP NO]],WK1_ATT[[#All],[CODE]],{"ADL","AL","BL","BLC","CP","CSL","CTL","FMC","FMCL","FMEC","FMLA","FMPL","HOT","IOD","MLT","PD","PPL","W"}))>1,1,SUM(COUNTIFS(WK1_ATT[[#All],[ATT DATE]], MIN(WK1_ATT[[#All],[ATT DATE]])+6,WK1_ATT[[#All],[EMP NO]],[@[EMP NO]],WK1_ATT[[#All],[CODE]],{"ADL","AL","BL","BLC","CP","CSL","CTL","FMC","FMCL","FMEC","FMLA","FMPL","HOT","IOD","MLT","PD","PPL","W"})))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)