# Counting one column when criteria from another column is met

Options

Hi! I would like to count the number of cells in one column that is not blank when another column is "HR" or "SURVIVOR". My current formula is below and is returning a #UNPARSEABLE error.

=COUNTIFS([APP./RETURN DATE]:[APP./RETURN DATE]), <> "", [RISK?]:[RISK?], "HR", "SURVIVOR")

Thank you!

Thu

Tags:

## Best Answers

• Employee Admin
Answer ✓
Options

You'll want to wrap the IFERROR around both of your VLOOKUP formulas, like so:

=IFERROR(VLOOKUP(formula), "")

Does that make sense? See: IFERROR Function

Cheers,

Genevieve

• Employee Admin
Answer ✓
Options

Are you looking to exclude values that contain "CPC" then, not just "CPC" exactly?

Try using AND instead of OR:

=COUNTIFS([APP./RETURN DATE]:[APP./RETURN DATE], AND(@cell <> "OTHER", @cell <> "LVM 1", @cell <> "LVM 2", @cell <> "CPC-LVM", @cell <> "CPC", @cell <> "UNABLE TO REACH BY PHONE", @cell <> "LVM 3", @cell <> "SCHEDULE AFTER 6-MONTHS", @cell <> "N/A"), [RISK?]:[RISK?], @cell = "AR")

• Employee Admin
Answer ✓
Options

Thanks for clarifying!

Did you try the formula with AND instead of OR?

This then says, don't include this AND don't include this AND don't include this... etc.

«1

## Answers

• ✭✭✭✭✭
Options

Give this a try

=COUNTIFS([APP./RETURN DATE]:[APP./RETURN DATE], <>"", [RISK?]:[RISK?], OR(@cell = "HR", @cell = "SURVIVOR"))

• Options

Thank you so much, Devin! So, it's giving me a different error - #NOMATCH. Do you think it's because of the <>"" part of the function? I want it to count cells that have are not blank.

• Employee Admin
Options

Do you have a formula in either the [APP./RETURN DATE] or the  [RISK?] column?

If there's an error in either of these columns it will appear in your COUNTIFS column as well.

You can wrap an IFERROR function around the other formula, like so:

=IFERROR(formula, "")

If this hasn't helped, please provide a screen capture of your sheet with the formula open, but block out sensitive data.

Thanks!

Genevieve

• Options

I do have a VLOOKUP (cell linking) in both columns. Where would I input the =IFERROR function?

Thank you so much!

Thu

• Employee Admin
Answer ✓
Options

You'll want to wrap the IFERROR around both of your VLOOKUP formulas, like so:

=IFERROR(VLOOKUP(formula), "")

Does that make sense? See: IFERROR Function

Cheers,

Genevieve

• Options

That worked! Now my problem is that it's not giving me the correct number. I checked it against doing my own filters and also creating report. The function I used in the Summary for the sheet is giving me 3 more than what I should have. Any thoughts on that one? Thank you!

• Employee Admin
Options

What type of column is the [RISK?] column? Is it possible that "HR" and "SURVIVOR" can both be selected in the same cell?

It would be helpful to see a screen capture of how your sheet is set up, if possible, but please block out sensitive data.

Cheers,

Genevieve

• Options

Hi @Genevieve P.! See screen capture below. The risk column is single select...

The summary function is: =COUNTIFS([APP./RETURN DATE]:[APP./RETURN DATE], OR(@cell <> "OTHER", @cell <> "There is an error"), [RISK?]:[RISK?], OR(@cell = "HR", @cell = "SURVIVOR"))

Thank you again!

Thu

• Options

I think I actually fixed my problem - hooray! I took out @cell <> "There is an error".

My next problem: I am trying to use this same formula, but modified, to count "AR" instead of "HR" or "SURVIVOR". My formula is below - do you see anything wrong with it?

=COUNTIFS([APP./RETURN DATE]:[APP./RETURN DATE], OR(@cell <> "OTHER", @cell <> "LVM 1", @cell <> "LVM 2", @cell <> "CPC-LVM", @cell <> "CPC", @cell <> "UNABLE TO REACH BY PHONE", @cell <> "LVM 3", @cell <> "SCHEDULE AFTER 6-MONTHS", @cell <> "N/A"), [RISK?]:[RISK?], @cell = "AR")

Thanks again! You've been such a HUGE help!

Thu

• Employee Admin
Options

OR is tricky in COUNTIFS when you're using <> (not). This is because a cell that is <> "OTHER" could then be "LVM 1".

Instead of using OR(@cell <> .... etc, try listing the values that it COULD be.

=COUNTIFS([APP./RETURN DATE]:[APP./RETURN DATE], OR(@cell = "Value", @cell = "Value"... etc), [RISK?]:[RISK?], @cell = "AR")

Does that make sense?

Cheers!

Genevieve

• Options

I thought about doing that, but my problem is there are many, many, MANY values it could be b/c they are actual dates (and some with dates and text...). Is there another formula that you know of that would count dates within a cell even if it has text with it (example of a cell with text in it: "3/1/21 CPC")

Thank you!

Thu

• Employee Admin
Answer ✓
Options

Are you looking to exclude values that contain "CPC" then, not just "CPC" exactly?

Try using AND instead of OR:

=COUNTIFS([APP./RETURN DATE]:[APP./RETURN DATE], AND(@cell <> "OTHER", @cell <> "LVM 1", @cell <> "LVM 2", @cell <> "CPC-LVM", @cell <> "CPC", @cell <> "UNABLE TO REACH BY PHONE", @cell <> "LVM 3", @cell <> "SCHEDULE AFTER 6-MONTHS", @cell <> "N/A"), [RISK?]:[RISK?], @cell = "AR")

• Options

Actually, quite the opposite - trying to include values that have a date with CPC and just CPC in the App./Return Date column - but here are so many values because the column could contain any date combination with CPC. I thought the easier way would just exclude all the values I do not want to include, which are OTHER, LVM 1, LVM 2, CPC-LVM, CPC, UNABLE TO REACH BY PHONE, LVM 3", SCHEDULE AFTER 6-MONTHS, and N/A. Hope that makes sense?

Thank you!

• Employee Admin
Answer ✓
Options

Thanks for clarifying!

Did you try the formula with AND instead of OR?

This then says, don't include this AND don't include this AND don't include this... etc.

• Options

YAY!! THANK YOU SO MUCH FOR HELPING FIX THAT!! IT WORKS! 😁

Happy holidays!

Thu

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!