Nested IF isn't working

Alex Hackford

Hi, I'm trying show offers declined for a certain region by month with the following formula:

=IF(COUNTIFS({TA_Offer Decline data Range 3}, $Region@row, {TA_Offer Decline data Range 1}, "Product Management", {TA_Offer Decline data Range 1}, "Engineering", {TA_Offer Decline data Range 1}, "IT", {TA_Offer Decline data Range 1}, "Security", {TA_Offer Decline data Range 1}, "Cloud Ops", {TA_Offer Decline data Range 2}, $May$2) = 0, "", (COUNTIFS({TA_Offer Decline data Range 3}, $Region@row, {TA_Offer Decline data Range 1}, "Product Management", {TA_Offer Decline data Range 1}, "Engineering", {TA_Offer Decline data Range 1}, "IT", {TA_Offer Decline data Range 1}, "Security", {TA_Offer Decline data Range 1}, "Cloud Ops", {TA_Offer Decline data Range 2}, $May$2)))

I'm guessing it's an issue w/ my nested IF(COUNTIF but hitting a wall...here's what the sheet looks like. The zeros being returned are incorrect.

Jon Mark H

Hey Alex -

Correct me if I'm wrong - but it looks like you want to look at offers declined PER Type or department?

If that's the case - it seems your COUNTIFS isn't working, because you actually want to do multiple COUNTIFS and sum them - (I only did the 1st half of what I think you'll need)

Where each COUNTIFS checks to reference sheet for the Region match, the individual department match & the date reference match, then sums together.

=IF(SUM(COUNTIFS({TA_Offer Decline data Range 3}, $Region@row, {TA_Offer Decline data Range 1}, "Product Management", {TA_Offer Decline data Range 2}, $May$2), COUNTIFS({TA_Offer Decline data Range 3}, $Region@row, {TA_Offer Decline data Range 1}, "Engineering", {TA_Offer Decline data Range 2}, $May$2), COUNTIFS({TA_Offer Decline data Range 3}, $Region@row, {TA_Offer Decline data Range 1}, "IT", {TA_Offer Decline data Range 2}, $May$2), COUNTIFS({TA_Offer Decline data Range 3}, $Region@row, {TA_Offer Decline data Range 1}, "Security", {TA_Offer Decline data Range 2}, $May$2), COUNTIFS({TA_Offer Decline data Range 3}, $Region@row, {TA_Offer Decline data Range 1}, "Cloud Ops", {TA_Offer Decline data Range 2}, $May$2)) = 0, "", **Insert second half here**

Let me know if that helps or not!

I'd love to take another crack at it if that doesn't solve your issue.

-Jon

Sameer Karkhanis

The reason you are getting zeroes is coz you are trying to match all of those conditions in a single range {TA_Offer Decline data Range 1} and if will always return false/0 as you cannot have all values in the same cell. Assuming you are looking for the count of all those cells in the column where one of those values exists you can try the below,

=IF(
COUNTIFS({TA_Offer Decline data Range 3}, $Region@row, {TA_Offer Decline data Range 1}, OR(@cell ="Product Management", @cell ="Engineering", @cell = "IT", @cell = "Security", @cell = "Cloud Ops"), {TA_Offer Decline data Range 2}, $May$2) = 0,
"",
COUNTIFS({TA_Offer Decline data Range 3}, $Region@row, {TA_Offer Decline data Range 1}, OR(@cell ="Product Management", @cell ="Engineering", @cell = "IT", @cell = "Security", @cell = "Cloud Ops"), {TA_Offer Decline data Range 2}, $May$2)
)

Alex Hackford

Thank you both for your comments. Sameer's formula above gave me what i need.

Cheers,

Alex