Countif help where range column contains column formula and text

Alexis Kipping

I'm trying to create a summary metric of my active projects using countif. My project status column contains the following column formula, which works fine.

=IF([Current Stage]@row = "Stage 5", "Active", IF([Current Stage]@row = "Stage 4", "Active", IF([Current Stage]@row = "Stage 3", "Active", IF([Current Stage]@row = "Stage 2", "Active", IF(AND([Current Stage]@row = "NSG", [Gate 5 - 1st Available Ship Date]@row <> ""), "Launched", IF([Current Stage]@row = "NSG", "Active", IF([Current Stage]@row = "n/a", "Inactive", IF([Current Stage]@row = "PLR", "Launched"))))))))

The sheet summary formula I have is below and I get #no match. There are plenty of projects where project status = active.

=COUNTIF([Project Status]:[Project Status], "Active")

What am I doing wrong?

Paul Newcome

Are you able to provide a screenshot?

Paul Newcome

I would also like to suggest the below as a simplified version of your nested IF.

=IF([Current Stage]@row = "n/a", "Inactive", IF(OR(AND([Current Stage]@row = "NSG", [Gate 5 - 1st Available Ship Date]@row <> ""),[Current Stage]@row = "PLR"), "Launched", "Active"))

We take care of the only instance of "Inactive" first, then move on to check for either of the two "Launched" criteria using an OR function, and finally just say that if it does not meet either of those two outputs then it must be "Active".

Alexis Kipping

Here is a screen shot.  Thank you for the help.  Double thanks for simplified project status

formula

Alexis Kipping

Paul Newcome

I am not seeing a screenshot. Could you please try to re-upload it?

Alexis Kipping

I am having trouble with getting the screen shot attached.

Screen shot.JPG

Paul Newcome

That formula should be working. Let's try a minor tweak first.

=COUNTIF([Project Status]:[Project Status], @cell = "Active")

Alexis Kipping

it still returns #no match with the updated formula. I ended up creating another sheet just for formulas and was able to get my original formula to work in the new sheet by referencing the original sheet.

I was also able to get an alternate countif situation to work in the same sheet as above, but the range was a different column, which makes me wonder if somehow the column formula in [Project Status] is some how causing an error to happen.

Thank you for your help.

Paul Newcome

I haven't seen column formulas causing any issues with cross sheet references before.

@Genevieve P Have you seen anything like this before?

Genevieve P.

Hi @Alexis Kipping & @Paul Newcome

Yes! I've seen this type of thing happen when the column that your COUNTIF formula is looking into has an error somewhere in it... like a domino effect.

Your COUNTIF formula isn't trying to match anything at all, it's looking at an entire range and searching for a value. Since you're receiving an error (#nomatch) that doesn't make sense for the function (Countif), this indicates to me that there's an error in your referenced column ([Project Status]).

Now, since your IF statement in the referenced column ALSO doesn't make sense with this error, do any of the referenced columns in this column formula contain another formula?

[Current Stage] or  [Gate 5 - 1st Available Ship Date] ?

Your IF statement is looking in its own row. This means the error would only appear in one cell. However your COUNTIF formula looks at the entire [Project Status] column, so just one cell with an error will throw the whole thing off, and present the #nomatch.

Are there formulas in [Current Stage] or  [Gate 5 - 1st Available Ship Date] ? If so, are either of these formulas returning #nomatch? We can clean this up by adding an IFERROR statement around them.

Let me know if this was the cause or not!

Cheers,

Genevieve

Paul Newcome

@Genevieve P I can't believe I forgot to check the ranges for the error. Ugh. Time for more coffee. Haha. Thanks for chiming in!

Genevieve P.

Haha no problem! Let's see if my guess is correct...

https://media.giphy.com/media/McjDXkZKw2v8A/giphy.gif

Alexis Kipping

They are formulas in current stage and gate 5 - 1st available ship date, but there are no errors in any of the rows of those columns.  I have also updated my projects status column formula to this condensed version Paul provided

=IF([Current Stage]@row = "n/a", "Inactive", IF(OR(AND([Current Stage]@row = "NSG", [Gate 5 - 1st Available Ship Date]@row <> ""),[Current Stage]@row = "PLR"), "Launched", "Active"))

Genevieve P.

Hi @Alexis Kipping

What are the formulas in the other two columns? Are you able to copy/paste those into this thread?

We could also just add the IFERROR statement around the Project Status column formula to test and see if this is the issue:

=IFERROR(IF([Current Stage]@row = "n/a", "Inactive", IF(OR(AND([Current Stage]@row = "NSG", [Gate 5 - 1st Available Ship Date]@row <> ""),[Current Stage]@row = "PLR"), "Launched", "Active")), "")

Alexis Kipping

@Genevieve P Thank you for your assistance.  Appreciate both your and @Paul Newcome's guidance :)

Project Status =IF([Current Stage]@row = "n/a", "Inactive", IF(OR(AND([Current Stage]@row = "NSG", [Gate 5 - 1st Available Ship Date]@row <> ""), [Current Stage]@row = "PLR"), "Launched", "Active"))

Current Stage =IF([Project Type]@row = "NSG", "NSG", IF(AND([Gate 5 Decision]@row = "Approved", [Gate 5 - 1st Available Ship Date]@row <> ""), "PLR", IF(OR(CONTAINS("Cancelled", [NPR Decision]@row:[Gate 5 Decision]@row), CONTAINS("On-hold", [NPR Decision]@row:[Gate 5 Decision]@row)), "n/a", "Stage " + (COUNTIFS([NPR Decision]@row:[Gate 5 Decision]@row, "Approved") + 1))))

Gate 5 - 1st Shipment Date is actually a date column that is entered, and not a formula.   I misspoke. 

and if needed, [Project Type] referenced in [Current Stage] is also a formula

Project Type = =VLOOKUP([Innovation Type]@row, {GPN Dashboard Look-up Table Range 1}, 2, false)

There are no errors in any of the other columns.