COUNTIFS with Multiple Criteria

sduffer

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!

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

sduffer

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

Genevieve P.

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