COUNTIFS with Multiple Criteria

HI all, hoping that someone can help me out please.

I have two spreadsheets. One is a tracker, and one is to collect metrics.

On the Metrics sheet, I want to count all of the items in the tracker sheet in one column, that meet one of three criteria from a second column, and then also fit within a range in a third column.

Here are a couple of my attempts (this has eaten up my day today):

=COUNTIFS({Proposal Tracking CY2023 Range 4}, {Proposal Tracking CY2023 Range 3}, "4 - Delivered", OR {Proposal Tracking CY2023 Range 3}, "5 - Awarded", OR {Proposal Tracking CY2023 Range 3}, "6 - Not Awarded", {Proposal Tracking CY2023 Range 5} > 0, {Proposal Tracking CY2023 Range 5, <1000000})

=COUNTIFS({Proposal Tracking CY2023 Range 4}, 1, [{Proposal Tracking CY2023 Range 3}, OR(@cell =“4 – Delivered”, “5 – Delivered”, “6 – Delivered”)], [{Proposal Tracking CY2023 Range 5}, >0], [{Proposal Tracking CY2023 Range 5}, <1000000])


Here's a (very hastily and poorly) redacted screenshot of the Tracker spreadsheet. Any help greatly appreciated!


Answers

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭

    You can use AND and OR functions with the @cell reference to collect multiple metrics for your COUNTIFS function, so you are on the right track with your 2nd attempt. Below is an example where the Status:Status part of the countif returns the 6 blue values, and the Price:Price part returns the 3 values in yellow. The intersection is 2 values which the formula then returns.

    Using your ranges, I think it'd be something like this:

    =COUNTIFS({Proposal Tracking CY2023 Range 4}, true, {Proposal Tracking CY2023 Range 3}, OR(@cell = "4 - Delivered", @cell = "5 - Awarded", @cell = "6 - Not Awarded"), {Proposal Tracking CY2023 Range 5}, AND(@cell > 0, @cell <1000000}))

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

  • sduffer
    sduffer ✭✭

    Thanks Jason! It still came back unparseable, but I feel like your answer is getting me closer...

  • Hey @sduffer

    It looks like there's just an extra } at the end that doesn't need to be there. Try it again:

    =COUNTIFS({Proposal Tracking CY2023 Range 4}, true, {Proposal Tracking CY2023 Range 3}, OR(@cell = "4 - Delivered", @cell = "5 - Awarded", @cell = "6 - Not Awarded"), {Proposal Tracking CY2023 Range 5}, AND(@cell > 0, @cell <1000000))

    If this doesn't work, please post a screen capture of how you have the formula typed in the cell, but block out sensitive data.

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!