CountIf Not Working with TRUE Criterion

Hello. I'm creating a schedule and have an IF/AND formula working perfectly for the days the employees will work:

example: =IF((AND(Sunday!BO4=TRUE,'Days of the Week'!C4=TRUE)),"B",IF((AND(Sunday!BP4=TRUE,'Days of the Week'!C4=TRUE)),"B",IF((AND(Sunday!BO4=FALSE,'Days of the Week'!C4=TRUE)),"TRUE",IF((AND(Sunday!BP4=FALSE,'Days of the Week'!C4=TRUE)),"TRUE","FALSE"))))

Conditional formatting is set to fill with color depending on the result. This all works.

My problem is in trying to COUNTIF the cell has TRUE. It's not counting and I'm not sure why. I need other formulas to recognize the results of the IF/AND formula. Any idea what the problem may be?

CountIf formula: =IFERROR(SUMPRODUCT(COUNTIF(B4:B18, TRUE)),0)


Thank you for any and all assistance!

Wouldnt you want to place your IF formula in as your "TRUE" parameter for the IFERROR?

if im reading that formula correctly, its only returning TRUE if those conditions are met correct?
And FALSE if not..

You can try it on one or two and see..

So the end formula would look like this:

=IFERROR(SUMPRODUCT(COUNTIF(B4:B18, IF((AND(Sunday!BO4=TRUE,'Days of the Week'!C4=TRUE)),"B",IF((AND(Sunday!BP4=TRUE,'Days of the Week'!C4=TRUE)),"B",IF((AND(Sunday!BO4=FALSE,'Days of the Week'!C4=TRUE)),"TRUE",IF((AND(Sunday!BP4=FALSE,'Days of the Week'!C4=TRUE)),"TRUE","FALSE")))))),0)

Without a sample file to work with, im just guessing if that structure is correct.

If thats not returning the correct results or any at all, it may need to be a mix of another function to get what you need.

There are three possible results: B, TRUE, FALSE. I only want TRUE to be counted.

Please find the sample attached. For context, the Shift, Lunch, and Break times are supposed to be highlighted on the corresponding day tabs (i.e. if on the Agent Work Week tab, the cell for Monday is green and times are entered, then the Monday tab should have the corresponding time cells also filled with green.) This is why I need the formula results to be recognized by other formulas.

Would you be able to update the sample file with some test data so its a little easier to test why things are not working?

Found the problem and it wasnt really obvious right away..
You have a mix of "TRUE" and TRUE within the formulas

In your Rush file, in cell B19 place this formula:

Please Login or Register  to view this content.

Then in B18 enter this formula and copy up to B4

Please Login or Register  to view this content.

The number of agents will change from 0 to the number in your sheet, at least it changed on my copy. But not sure its right.. my copy went from 0 to 11 once i updated all "TRUE" and "FALSE" to TRUE and FALSE(Remove the quotes)

See if that gets you what you need or if it still needs adjustments.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new I have done it for you today.)

Awesome, if you felt it help, consider clicking the add reputation on the post that helped.

Glad its working for you now.