COUNTIFS with Multiple Criteria

Options

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

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

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

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!