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!

Find more posts tagged with

Accepted answers

All comments

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:

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.