COUNT formula that will use multiple criteria to sum how many targets have meet met

[ArcticFoxes]

Hi all, I've been stuck trying to write a formula that will count if monthly customer contact targets are being met.
I currently have two conditional formatting rules in place which highlight if the targets are met or not.

My aim is to calculate the number of occurrences targets are either met or not.

The data is similar to this dummy version:

Monthly Targets.png

The conditional formatting rule that highlights targets not being met is: = AND(VALUE($H3) = VALUE(B$2) , VALUE(B3) > VALUE($G3))
This first identifies which Month column to look at, using the Target Months column; it will then highlight if the number of calls is less than the target calls for that client.

The correct COUNT numbers should be: 2 targets not met & 5 targets met. TIA!

[GeoffW283]

Welcome to the forum

The easy way would be to add a new column I:I titled "Met?" and populate it with your CF formula.

Please Login or Register  to view this content.

Then simply =countifs(I3:I7, "TRUE") and =countifs(I3:I7, "FALSE")

BTW are the value() calls really necessary? Are all your cells text rather than numbers??

If this is not what you are looking for then please upload a sample workbook per the instructions in the yellow banner at the top of the thread.

[ArcticFoxes]

Hi Geoff, thanks for the response.

The problem is that it would require 5 columns for each month and the number of rows exceeds 5000.

I had to add the VALUE() calls as some of the cells are automatically filled in as text (01 or 1 etc.), maybe there is a more elegant way of solving this issue.

I've attached a sample worksheet if that helps explain the issue a bit better.

[AliGW]

There is no workbook attached.

EDIT: There is now!

[GeoffW283]

OK, 1 helper column:

In J3 copied down to J9:

Please Login or Register  to view this content.

Then in H13:

Please Login or Register  to view this content.

And H14:

Please Login or Register  to view this content.

See attached update to your workbook

[josephteh]

Geoff, your formula for J3 will not work properly for 6th and 12th months because there is no continuous count of months in row 2.

[Gregb11]

Since you are using 365, you should be able to use these formulas:

To get the targets met, try this:

=SUMPRODUCT((Table1[[1]:[12]]>=Table1[Target Calls])*(Table1[Target Months]=VALUE(Table1[[#Headers],[1]:[12]])))

To get the No Met, try:

=SUMPRODUCT((Table1[Target Months]=VALUE(Table1[[#Headers],[1]:[12]]))*(Table1[[1]:[12]]< Table1[Target Calls]))

This matches the amounts you gave, but as a note, I believe you may have part of the criteria backwards for the NOT MET - to get 2 as the answer, the number inside the grid needs to be LESS THAN the Target Calls, not Greater than.

[ArcticFoxes]

Thanks for the solution! It works well for the sample, however when I try and write the formula for my actual data set it returns 0 for the met and just the number of rows for the no met.

I've added another sample workbook with data formatted like in my actual data set. I'm not able to rename the actual table headers, as they are pulled from a main database, so instead the months 1-12 are referenced in the row above the headers.

I didn't fully explain my aim in the original post, as I also want to separate the target counts by salesperson. I'll update if I can get it working

[davsth]

Sales call target is also text , so you need to do the same thing as you did with Enqfreq target

= SUMPRODUCT((Table_Query_from_DB[[ThisMth]:[12Mth]]>=VALUE(Table_Query_from_DB[SalesCallsTgt]))*(VALUE(Table_Query_from_DB[EnqFreqTgt])=VALUE(B4:F4)))

otherwise all your first condition is false

[ArcticFoxes]

Amazing! I also filtered it by salesperson by adding in an extra condition

Please Login or Register  to view this content.

Thank you everyone for the assistance, I learnt a lot along the way