Calculating 2 criteria from one column up to a specific date, also including a check box reference

I'm trying to calculate data from a status column that has drop down options of 'in progress' and 'not started' and see how many from those 2 criteria return an end date up to the end of June 2024 (working days will be 28 June). Also ensuring that a check box for if it's being reported is checked. I'm using a sheet reference, column 3 is the status, 6 is the date range and 1 is the checkbox for reporting; so my current formula is this:

=COUNTIFS({Project name 3}, "in progress", {Project name Range 3}, "not started", {Project name 6}, <DATE(2024, 6, 28), {Project name 2}, "1"

It's not returned an error message, just a zero - and the amount should actually be 24.

My calculation for all activities that end after June 28 that are reported on works,

=COUNTIFS({Project name Range 6}, >DATE(2024, 6, 28), {Project name Range 2}, "1")

so it's the additional parameters of looking at the status which is stumping me! Appreciate any help, thanks.

Find more posts tagged with

Formulas

Countif

Accepted answers

Bassam Khalil

Hi @Bek T,

please try the following formula:

=COUNTIFS({Project name Status}, "in progress", {Project name End Date}, <=DATE(2024, 6, 28),
 {Project name Reporting}, 1) + COUNTIFS({Project name Status}, "not started",
 {Project name End Date}, <=DATE(2024, 6, 28), {Project name Reporting}, 1)

In this formula:

Replace {Project name Status} with the correct reference for your status column (which seems to be {Project name 3}).
Replace {Project name End Date} with the correct reference for your end date column (assumed to be {Project name 6} based on your description).
Replace {Project name Reporting} with the correct reference for your reporting checkbox column (you mentioned {Project name 2} but ensure this is correct).

This approach sums the counts of "in progress" and "not started" items that also meet the end date and reporting criteria, which seems to align more closely with your requirements.

Please make sure to adjust the column references as per your actual sheet's setup, as it seems there might have been a slight confusion or typo in the references you've provided.

All comments

Bassam Khalil

Hi @Bek T,

please try the following formula:

=COUNTIFS({Project name Status}, "in progress", {Project name End Date}, <=DATE(2024, 6, 28),
 {Project name Reporting}, 1) + COUNTIFS({Project name Status}, "not started",
 {Project name End Date}, <=DATE(2024, 6, 28), {Project name Reporting}, 1)

In this formula:

Replace {Project name Status} with the correct reference for your status column (which seems to be {Project name 3}).
Replace {Project name End Date} with the correct reference for your end date column (assumed to be {Project name 6} based on your description).
Replace {Project name Reporting} with the correct reference for your reporting checkbox column (you mentioned {Project name 2} but ensure this is correct).

This approach sums the counts of "in progress" and "not started" items that also meet the end date and reporting criteria, which seems to align more closely with your requirements.

Please make sure to adjust the column references as per your actual sheet's setup, as it seems there might have been a slight confusion or typo in the references you've provided.

Bek T

Hey Bassam, thanks so much for this. It worked!

And apologies for any confusion with the project name fields - I manually changed them in the question box to be more generic, but see where I missed one (brain capacity after trying too many countifs variations at the end of a day!)

Ngā mihi :)

Bassam Khalil

You are welcome @Bek T

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.