Counting one column when criteria from another column is met

Thu Nghiem

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

Genevieve P.

Hi @Thu Nghiem

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

Genevieve P.

Hi @Thu Nghiem

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")

Genevieve P.

Hi @Thu Nghiem

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.

Devin Lee

Give this a try

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

Thu Nghiem

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.

Genevieve P.

Hi @Thu Nghiem

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

Thu Nghiem

Hi @Genevieve P.!

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

Thank you so much!

Thu

Genevieve P.

Hi @Thu Nghiem

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

Thu Nghiem

Hi! @Genevieve P.

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!

Genevieve P.

Hi @Thu Nghiem

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

Thu Nghiem

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

Thu Nghiem

Hi @Genevieve P.!

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

Genevieve P.

Hi @Thu Nghiem

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

Thu Nghiem

Hi @Genevieve P. !

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

Genevieve P.

Hi @Thu Nghiem

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")

Thu Nghiem

Hi @Genevieve P.

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!

Genevieve P.

Hi @Thu Nghiem

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.

Thu Nghiem

Hi @Genevieve P. !

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

Happy holidays!

Thu