Countif value matches, but ignore/don't count if value in adjacent column matches value

[nickpavlov]

I am using the following formula to track employee?s attendance occurrences, plus a few other items.

=IF(A15="","",IF(B15>$D$2,"N/A",IF(COUNTIFS('Data Entry'!$B$3:$B$99989,A15,'Data Entry'!$E$3:$E$99989,$D$1,'Data Entry'!$C$3:$C$99989,"Perfect Redeemed"),"PERFECT REDEEMED",IF(COUNTIFS('Data Entry'!$B$3:$B$99987,$A15,'Data Entry'!$E$3:$E$99987,$D$1),"PERFECT USED",IF(COUNTIFS('Data Entry'!$B$3:$B$1048576,A15,'Data Entry'!$A$3:$A$1048576,">="&$D$2,'Data Entry'!$A$3:$A$1048576,"<="&$D$3)=0,"PERFECT ATTENDANCE",COUNTIFS('Data Entry'!$B$3:$B$1048576,A15,'Data Entry'!$A$3:$A$1048576,">="&$D$2,'Data Entry'!$A$3:$A$1048576,"<="&$D$3))))))

The first part of the formula is counting occurrences that the employee?s name shows up on the data entry worksheet.

=IF(A15="","",IF(B15>$D$2,"N/A",IF(COUNTIFS('Data Entry'!$B$3:$B$99989,A15,

What I am looking to do, is ignore/don?t count if the corresponding column ?Data Entry?!$C$3:$C$100000 contains the value of ?Other (See Comments)? or ?Positive Entry?.

The results would count all other instances where that employee name showed up when those values were not in column C.

This is pretty complicated, I had assistance making the original formula on here. Attached is a sample file.

I appreciate any help on this one.

Thank you,
Nick

[thomglea]

That formula is a beast. When crafting those monstrosities, I like to build them in pieces. Start at the first blank column at the end of the row, then keeping adding columns with additional formulas that make use of the result in the previous formula. When you get to the end, and you get the expected results, start working backwards, substituting back in until you get back to just one formula.

That said, with 100,000 rows of data being used, you should really convert that data set into a table. Select it all and press CTRL+T. In your formula, I can see ranges differing between different sections of your formula. At the beginning you go to row 99989, but near the end you have row 1048576.

Finally, as to how to ignore/don't count something is easy. In the COUNTIFS formula, add a criteria of "<>Value to Ignore" something like:

Please Login or Register  to view this content.

[nickpavlov]

Thank you Thomglea, I am going to see what I can come up with.

[nickpavlov]

I cleaned up the formula and came up with the following, no errors.

But I don't want it to ignore <> Prefect Redeemed from the second portion of the formula, 'Data Entry'!$E:$E,$D$1,'Data Entry'!$C:$C,"Perfect Redeemed"),"PERFECT REDEEMED"

I am just looking for it to ignore if there is a count returned.

=IF(A6="","",IF($B6>$D$2,"N/A",IF(COUNTIFS('Data Entry'!$C:$C,”<>PERFECT REDEEMED”,'Data Entry'!$B:$B,$A6,'Data Entry'!$E:$E,$D$1,'Data Entry'!$C:$C,"Perfect Redeemed"),"PERFECT REDEEMED",IF(COUNTIFS('Data Entry'!$B:$B,$A6,'Data Entry'!$E:$E,$D$1),"PERFECT USED",IF(COUNTIFS('Data Entry'!$B:$B,$A6,'Data Entry'!$A:$A,">="&$D$2,'Data Entry'!$A:$A,"<="&$D$3)=0,"PERFECT ATTENDANCE",COUNTIFS('Data Entry'!$B:$B,$A6,'Data Entry'!$A:$A,">="&$D$2,'Data Entry'!$A:$A,"<="&$D$3))))))

[thomglea]

You are already using the IF conditional in there, so you can do something along the lines of below. The formula, translated to English would read: If the count of all this criteria is zero, then do this. Otherwise, if the count is higher than 0, do that. Currently, you do not have the COUNTIF = to anything in your formula, so Excel implies you intend the result to be greater than 0.

Please Login or Register  to view this content.

Can switch it around as well

Please Login or Register  to view this content.

[Gregb11]

A few notes:

1. As thomglea has mentioned, consider putting Employee List in an Excel Table
2. You already have "Data Entry" in an Excel Table so use those references
3. Consider using IFS instead of IF to make it easier to read
4. To help me with large formulas like this, I use <ALT><Enter> to put the different IFS on a line by line basis. The IFS statement will go in order, line by line and if a line is TRUE, then it stops and does not continue with the logic.

Based on all that, here is the formula I would suggest using in D5. You can then copy this formula over to the other columns.

=IFS(
[@[START DATE]]>D$2,"N/A",
COUNTIFS(TData[Occurance],”<>PERFECT REDEEMED”,TData[Employee Name],[@All],TData[Perfect Attendance],D$1,TData[Occurance],"Perfect Redeemed"),"PERFECT REDEEMED",
COUNTIFS(TData[Employee Name],[@All],TData[Perfect Attendance],D$1),"PERFECT USED",
COUNTIFS(TData[Employee Name],[@All],TData[Date],">="&D$2,TData[Date],"<="&D$3)=0,"PERFECT ATTENDANCE",
TRUE,COUNTIFS(TData[Employee Name],[@All],TData[Date],">="&D$2,TData[Date],"<="&D$3))

Please Login or Register  to view this content.

Regarding your issue about "I am just looking for it to ignore if there is a count returned", I'm not fully understanding what you are wanting, but maybe implementing the above will help you with a solution, or try explaining it more, using specific scenarios.

[nickpavlov]

Thank you both, I have been playing around with this today.

I got the exclude to work: 'Data Entry'!$C:$C,"<>PERFECT REDEEMED", but I want to exclude another value also.

The below didn't work, is there a ways to include a second value to exclude?

'Data Entry'!$C:$C,"<>PERFECT REDEEMED",'Data Entry'!$C:$C,"<>Positive Entry"

Thank you,
Nick

[nickpavlov]

Never mind I got it to work, had a typo on other sheet.