Conditional Formatting Formula - Multiple Criteria - SUM, COUNT, Exemption, exception

Hello Folks,

I've included a workbook with sample data as well as expected highlighting/conditional formatting to occur.

The criteria is as follows:
Highlight if sum of column D (SOLD) for a single person listed in Column A (INITIALS) is greater than 100 WHILE.
Exemption: ANY rows which may contain "DONE" in column E (VERIFY)

EXCEPTION: If the person in column A (INITIALS) only has ONE non-"DONE" row assigned, DO NOT highlight.


NOTE: You can SORT Column A in the "Expected OUTPUTEFFECT" sheet to better arrange and see the desired effect better.

I'm looking for both a DYNAMIC Range and a STATIC Range formula (I hope that's good wording).
The amount of rows per day in my case can fluctuate day to day from anything between 100-1000. For example, the static formula should consider the range A1:F1000 for highlighting; while the dynamic one should probably just be $A:$F

The following dynamic formula KIND OF work, but eventually DIDN'T and absolutely takes a long time to process thus my request for a more STATIC range formula as well. (I'm bad at making these things work haha)

Please Login or Register  to view this content.

(Hope I hand-typed that well)

environment: Using Office 365, and uploading to SharePoint for teams use.

Thanks in advance to all who participate!

The logic in your formula is clsoe - But when you apply the CF, just select the first row A2:F2 of your table, and use the formula

=AND($E2<>"DONE",SUMIFS($D:$D,$A:$A,$A2,$E:$E,"<>DONE")>100,COUNTIFS($A:$A,$A2,$E:$E,"<>DONE")>1)

Then copy A2:F2 and paste formats over the rest of your data - DO NOT apply CF to the entire column - that is 6,000,000+ formulas that need to be calculated (and is why your sheet was so slow).

Note, too, that in your example output, there you also had some DONE cells with an extra space: "DONE " is not equal to "DONE" so use Data Validation on those cells.

Originally Posted by Bernie Deitrick

The logic in your formula is clsoe - But when you apply the CF, just select the first row A2:F2 of your table, and use the formula

=AND($E2<>"DONE",SUMIFS($D:$D,$A:$A,$A2,$E:$E,"<>DONE")>100,COUNTIFS($A:$A,$A2,$E:$E,"<>DONE")>1)

Then copy A2:F2 and paste formats over the rest of your data - DO NOT apply CF to the entire column - that is 6,000,000+ formulas that need to be calculated (and is why your sheet was so slow).

Note, too, that in your example output, there you also had some DONE cells with an extra space: "DONE " is not equal to "DONE" so use Data Validation on those cells.

Thanks so much! It was the "DONE " which was throwing me for a loop; I didn't think about that possibility. Thanks for the extra set of eyes and adding that

Please Login or Register  to view this content.

bit; I forgot to add that bit from my hand type. I now have added data validation using a range table (of 1 cell) so that I may bypass case sensitivity; seems there was no option within the data validation itself.

It seems even with the limited CF Range 1:500(rows), it still is a bit slow Would you happen to have any suggestions for optimizing efficiency? (Hopefully without the obvious, "Reduce the CF Range from 500 to exactly what you need" - trying to keep it somewhat dynamic regardless of data size)

Originally Posted by Zelminar

Would you happen to have any suggestions for optimizing efficiency? (Hopefully without the obvious, "Reduce the CF Range from 500 to exactly what you need" - trying to keep it somewhat dynamic regardless of data size)

An easy thing is to only format one column (instead of 6) using the CF, or to not use CF at all, and add a column with the CF formula and then use that column as a filter (or use CF on that column to highlight TRUE values).

Oh, so when I decided to highlight the rows (6 column width) is that making the TRUE/FALSE calculation 6 times greater? Am I understanding that right? I could certainly give that a shot (only highlighting one column)

Every cell's CF formula gets calculated separately, so minimizing CF helps with the calculational overhead.