Need help with formula to count cells with conditiona

%3CLINGO-SUB%20id%3D%22lingo-sub-2187790%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20formula%20to%20count%20cells%20with%20conditiona%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2187790%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20which%20tracks%20SLA%20performance%20and%20there%20is%20currently%20a%20column%20with%20a%20target%20to%20hit%2C%20followed%20by%20another%20column%20that%20has%20the%20actuals%20in%20there.%20On%20the%20actual%20column%20I%20have%20added%20in%20some%20conditional%20formatting%20that%20will%20change%20the%20cell%20colour%20depending%20on%20whether%20the%20actuals%20are%20either%20in%20SLA%2C%20out%20of%20SLA%20or%20are%20equal%20to%20the%20target.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20to%20add%20another%20column%20which%20provides%20an%20aggregated%20count%20of%20how%20many%20agents%20are%20either%20in%20or%20out%20of%20SLA.%20I%20know%20that%20a%20formula%20can't%20count%20cell%20colours%20and%20would%20like%20to%20know%20the%20best%20way%20to%20give%20this%20aggregated%20view.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20spreadsheet%20that%20will%20hopefully%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2187790%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2187822%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20formula%20to%20count%20cells%20with%20conditiona%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2187822%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F984913%22%20target%3D%22_blank%22%3E%40DBreezy92%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20SLA%3A%20%3DSUMPRODUCT(--(C2%3AC11%26lt%3B%3DB2%3AB11))%3C%2FP%3E%0A%3CP%3EOut%20of%20SLA%3A%20%3DSUMPRODUCT(--(C2%3AC11%26gt%3BB2%3AB11))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

 

I have a spreadsheet which tracks SLA performance and there is currently a column with a target to hit, followed by another column that has the actuals in there. On the actual column I have added in some conditional formatting that will change the cell colour depending on whether the actuals are either in SLA, out of SLA or are equal to the target. 

 

I would to add another column which provides an aggregated count of how many agents are either in or out of SLA. I know that a formula can't count cell colours and would like to know the best way to give this aggregated view.

 

I have attached a spreadsheet that will hopefully help.

2 Replies

@DBreezy92 

In SLA: =SUMPRODUCT(--(C2:C11<=B2:B11))

Out of SLA: =SUMPRODUCT(--(C2:C11>B2:B11))

Thanks Hans, you've come through for me again!