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

Options

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

Best Answer

Answers

  • Tim Meeks
    Tim Meeks ✭✭✭✭✭✭
    Options

    @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.
    Genevieve P. Employee Admin
    Options

    Great! @Tim Meeks Happy to help!

  • AliT
    AliT ✭✭
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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
    AliT ✭✭
    Options

    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!!