Help with COUNTIFS formula

Hello!

I have a sheet that lists grant applications. Each row is a different application, and contains information about application due date (formatted as Date), application status (a drop down list), and many other things. I'd like to be able to automatically report on the number of in progress, pending, awarded, and unsuccessful applications during a given fiscal year. There are numerous reasons for an application to have been unsuccessful. All of the formulas that I'm using that refer to the drop down list are reporting a "0" count, which is far off. Can someone help me troubleshoot why this wouldn't be working?

For In Progress:

=COUNTIFS([Application Due Date]@row, >=DATE(2021, 7, 1), [Application Due Date]@row, <=DATE(2022, 6, 30), [Status/ Outcome]@row, "in progress")

For Pending:

=COUNTIFS([Application Due Date]@row, >=DATE(2021, 7, 1), [Application Due Date]@row, <=DATE(2022, 6, 30), [Status/ Outcome]@row, "pending")

For Not Funded:

=COUNTIFS([Application Due Date]@row, >=DATE(2021, 7, 1), [Application Due Date]@row, <=DATE(2022, 6, 30), [Status/ Outcome]@row, OR(@cell = "not funded", @cell = "not approved", @cell = "rejected", @cell = "not discussed"))

Find more posts tagged with

Accepted answers

KPH

Hi @owenhpdx

All of your logic is counting only the current row
[Application Due Date]@row Will look only at the cell in the [Application Due Date] column on the current row.

[Status/ Outcome]@row will look only at the cell in the [Status/ Outcome] column on the current row.

If you want to count ALL the rows where the logic is true you should reference the column, like this

[Status/ Outcome]:[Status/ Outcome]

[Application Due Date]:[Application Due Date]

Your formula

=COUNTIFS([Application Due Date]@row, >=DATE(2021, 7, 1), [Application Due Date]@row, <=DATE(2022, 6, 30), [Status/ Outcome]@row, "in progress")

Would become

=COUNTIFS([Application Due Date]:[Application Due Date],>=DATE(2021, 7, 1), [Application Due Date]:[Application Due Date], <=DATE(2022, 6, 30), [Status/ Outcome]:[Status/ Outcome], "in progress")

All comments

KPH

Hi @owenhpdx

All of your logic is counting only the current row
[Application Due Date]@row Will look only at the cell in the [Application Due Date] column on the current row.

[Status/ Outcome]@row will look only at the cell in the [Status/ Outcome] column on the current row.

If you want to count ALL the rows where the logic is true you should reference the column, like this

[Status/ Outcome]:[Status/ Outcome]

[Application Due Date]:[Application Due Date]

Your formula

=COUNTIFS([Application Due Date]@row, >=DATE(2021, 7, 1), [Application Due Date]@row, <=DATE(2022, 6, 30), [Status/ Outcome]@row, "in progress")

Would become

=COUNTIFS([Application Due Date]:[Application Due Date],>=DATE(2021, 7, 1), [Application Due Date]:[Application Due Date], <=DATE(2022, 6, 30), [Status/ Outcome]:[Status/ Outcome], "in progress")

owenhpdx

Thank you so much! I wondered about the '@row' logic, but I was going with what the AI suggested for my request for a formula to "count all rows"; apparently it wanted to count just one. Your formula worked great for the "in progress" query and I adapted it for the others and it worked great. Much appreciated!!!

KPH

Wonderful news. Always happy to be more useful than a robot 😉

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.