How to use Countifs with a cross reference to another sheet and having multiple criteria?

Patricia Brunstein

I am trying to utilize the CountIFS formula where it references one other sheet and two different columns. I used the formula:

=COUNTIFS({Sales Calls Summary Range 2}, "Other", [{Sales Calls Summary Range 1}, "Jan 6 - 10"])

I am receiving the error #Unparseable.

Can you tell me what is incorrect in the formula?

Thank you

Genevieve P.

Hi Patricia,

It looks like you just need to remove the square brackets around the second range and criteria... try this:

=COUNTIFS({Sales Calls Summary Range 2}, "Other", {Sales Calls Summary Range 1}, "Jan 6 - 10")

If you're following the pop-up helper window, those square brackets were to indicate that the next section is optional / quantity of range & criteria is up to you!

Let me know if that works. If not, it would be helpful to see a screen capture of the sheet you are referencing (but please block out any sensitive data).

Thanks!

Genevieve

Genevieve P.

Hi Patricia,

It looks like you just need to remove the square brackets around the second range and criteria... try this:

=COUNTIFS({Sales Calls Summary Range 2}, "Other", {Sales Calls Summary Range 1}, "Jan 6 - 10")

If you're following the pop-up helper window, those square brackets were to indicate that the next section is optional / quantity of range & criteria is up to you!

Let me know if that works. If not, it would be helpful to see a screen capture of the sheet you are referencing (but please block out any sensitive data).

Thanks!

Genevieve

Tim Meeks

@Genevieve P thanks! I just used it to solve an almost exact situation. Too many brackets and parenthesis to try and put in the correct plan. =COUNTIFS({CCAIR Site Range 1}, [Site Name]1, {CCAIR Child Status}, "Enrolled")

Genevieve P.

Great! @Tim Meeks Happy to help!

AliT

I just used the same guide as well to find all A&E contracts that are Executed.

=COUNTIFS({Sheet - Contract Management - Complete Lib Range 3}, "Executed", {Sheet - Contract Management - Complete Lib Range 4}, "A&E")

However, I am getting a 0 return which I know is not correct. The column that has the program type "A&E", is a dropdown list. Would that affect the formula?

Genevieve P.

Hi @AliT

Is the dropdown list multi-select or single select?

If it's multi-select, you'll want to use the HAS function to see if the column has this option selected with others, like so:

=COUNTIFS({Sheet - Contract Management - Complete Lib Range 3}, "Executed", {Sheet - Contract Management - Complete Lib Range 4}, HAS(@cell, "A&E"))

If this hasn't helped, double check that what you're searching for "in quotes" matches letter-for-letter the values that appear in your source sheet. You can test this by separating out your two instances into single COUNTIF formulas as well:

=COUNTIF({Sheet - Contract Management - Complete Lib Range 3}, "Executed")

=COUNTIF({Sheet - Contract Management - Complete Lib Range 4}, HAS(@cell, "A&E"))

This should help narrow down where the miscommunication is occurring.

Cheers!

Genevieve

AliT

Thank you! While it wasn't a multi-select column, when you suggested to review my formula I found that I was pointing one of them at the wrong column!! It is working now.

Thank you!!