COUNTIFS 4 variables

Jason H

Hi, I need help with this formula.

As you can see I am hoping to count when the name:

• Category Management is found in FUNCTION
• 100% is found in PERCENT COMPLETE
• S-CL is found in PERIOD and Day 1 is found in PERIOD

Goal is that if all attributes are found, the count and provide a total. My formula is below.

=COUNTIFS({Function}, "Category Management", {Percent}, "1", {Period}, "S-Cl", {Period}, "Day-1"

When I run this formula with just 3 attributes, which removes the Day 1 period - I gain results. So I know something is missing here, maybe a AND somewhere?

J

David Tutwiler

I think you're missing a CONTAINS function because you're requiring that the {Period} equals both S-CL and Day-1, which isn't possible. Maybe try:

=COUNTIFS({Function}, "Category Management", {Percent}, "1", {Period}, CONTINAS("S-Cl", @cell), {Period}, CONTAINS("Day-1", @cell))

Jason H

@David Tutwiler - NAILED IT! If I might ask one more formula question around this very same topic. With respect to the % Complete. Who might I encompass the formula you provided yet have the Percent look between a range.

Example -

·      Category Management is found in FUNCTION

·      Greater than or equal to 75% and less than or equal to 99% is found in PERCENT COMPLETE

·      S-CL is found in PERIOD and Day 1 is found in PERIOD

Thanks in advance for your insights and formula help!

David Tutwiler

For that I think you're looking for something like:

=COUNTIFS({Function}, "Category Management", {Percent}, AND(@cell > 0.74, @cell < 1) , {Period}, CONTINAS("S-Cl", @cell), {Period}, CONTAINS("Day-1", @cell))

Jason H

@David Tutwiler - I updated you string in order to pull the proper columns across. Sadly - I get 'unparseable' error.

Sharing my update formula based on yours.

=COUNTIFS({Finance_F}, "Risk Management", {Percent3}, AND(@cell > 0.50 @cell < 74) , {Finance_P}, CONTAINS("S-Cl", @cell), {Finance_P}, CONTAINS("Day-1", @cell))

I looked over this multiple times but didn't see any issues. Hoping I just missed or overlooked a small item

David Tutwiler

Just a few format things I think. A comma and a decimal place:

=COUNTIFS({Finance_F}, "Risk Management", {Percent3}, AND(@cell > 0.50, @cell < 0.74) , {Finance_P}, CONTAINS("S-Cl", @cell), {Finance_P}, CONTAINS("Day-1", @cell))

Jason H

@David Tutwiler - Formula worked, although it did not pull the data counts I'd expected. Sharing the VLOOKUP sheet I pull from has 5 within the criteria set out in the formula.

Any thoughts or ideas?

RM.PNG

David Tutwiler

Your formula says that it has to be greater than 50 and less than 74 to catch. I wouldn't be surprised if the formula returned a 0. If you want those to be included in the count you'll need to do:

=COUNTIFS({Finance_F}, "Risk Management", {Percent3}, AND(@cell > 0.49, @cell < 0.76) , {Finance_P}, CONTAINS("S-Cl", @cell), {Finance_P}, CONTAINS("Day-1", @cell))

Jason H

@David Tutwiler - It's interesting, because when I run the initial formula, count is 0

When I run this adjusted =COUNTIFS({Finance_F}, "Risk Management", {Percent3}, AND(@cell > 0.49, @cell < 0.76) , {Finance_P}, CONTAINS("S-Cl", @cell), {Finance_P}, CONTAINS("Day-1", @cell))

the count is 0

and then I adjusted it even further to say less than or equal to and still gained a count of 0

=COUNTIFS({Finance_F}, "Risk Management", {Percent3}, AND(@cell >= 0.5, @cell <= 0.74), {Finance_P}, CONTAINS("S-Cl", @cell), {Finance_P}, CONTAINS("Day-1", @cell))

This one is a bit perplexing!

David Tutwiler

I would check all of the rest of your arguments to make sure they come back true then. Consider even taking out this entire step just to make sure you get a non-zero number. The problem could be elsewhere.

For instance, I don't see Day-1 where you have S-Cl in your screenshot. That could be causing it to return 0.

Jason H

@David Tutwiler - Great point as we both know how exact the formulas need to be!

And to your point re the Day 1 / S-CL. My goal is to aggregate a total count of all variables including if either of these are showing, not just if 1 or both are showing.

Because of that - is there a formula adjustment to consider that says - count if EITHER are present?

David Tutwiler

Gotcha. So then you'll want to OR your last two variables:

=COUNTIFS({Finance_F}, "Risk Management", {Percent3}, AND(@cell >= 0.5, @cell <= 0.74), {Finance_P}, OR(CONTAINS("S-Cl", @cell), CONTAINS("Day-1", @cell)))

Jason H

@David Tutwiler - YOU ROCK! I can't thank you enough for sticking with me and sharing your personal time to help. I hope one day I can return the favor.

J

David Tutwiler

No problem. Glad it's working!

Jason H

@David Tutwiler - Up for one more formula question? Using the formula you created, I am now looking for any PERCENT COMPLETE fields that are, Blank.

Does my formula look inline

=COUNTIFS({Function}, "Category Management", {Percent}, "@BLANK", {Period}, CONTAINS("S-Cl", @cell), {Period}, CONTAINS("Day-1", @cell))

David Tutwiler

I think you need to change the "@BLANK" to ISBLANK() unless the cell literally says "@BLANK"