Countifs using cross sheet references

Sharon Castiglia

Hello,

I am trying to get a count of items with these Criteria

Stream = XXX

Window Start <>""

Survey Status = ""

Assignment Status =

Vendor Assigned

Vendor Scheduling

Late - requested update

This is the current formula I have which is populating a quantity of 0 (Actual count should be 325.

=COUNTIFS({Site Survey Stream}, $STREAM$1, {Window Start}, <>"", {Survey Status}, "", {Assignment Status}, "Vendor Assigned", {Assignment Status}, "Vendor Scheduling", {Assignment Status}, "Late - requested update")

Any help certainly would be very appreciated.

Sharon C.

Find more posts tagged with

Accepted answers

Paul Newcome

This is the problem:

{Assignment Status}, "Vendor Assigned", {Assignment Status}, "Vendor Scheduling", {Assignment Status}, "Late - requested update"

Having range/criteria/range/criteria, AND is implied which means that triggering a count would mean the cell was equal to all 3 of those things at the same time which isn't possible. Try using an OR statement like so:

=COUNTIFS({Site Survey Stream}, $STREAM$1, {Window Start}, <>"", {Survey Status}, "", {Assignment Status}, OR(@cell = "Vendor Assigned", @cell = "Vendor Scheduling", @cell = "Late - requested update"))

RossL

@Paul Newcome

you beat me to it nice answer

Paul Newcome

Make sure your formula is mimicking your filter. Your current formula currently states the the {Survey Review Date} must be greater than or equal to 6 Sept. AND 12 Sept. at the same time. I imagine one of those is probably supposed to be a less than.

All comments

Paul Newcome

This is the problem:

{Assignment Status}, "Vendor Assigned", {Assignment Status}, "Vendor Scheduling", {Assignment Status}, "Late - requested update"

RossL

@Paul Newcome

you beat me to it nice answer

Sharon Castiglia

One more Question:

Once again Mr. Newcome, you are Johnny on the Spot. Thank you so much. I have another issue:

Have the following formula:

=COUNTIFS({Site Survey Stream}, $STREAM$14, {Survey Status}, "Green", {Survey Reviewed Date}, >=(DATE(2020, 9, 6)), {Survey Reviewed Date}, >=(DATE(2020, 9, 12)))

Coming up with a sum of 0, but when I apply the filter on the source sheet it is showing a quantity of 7, what am I doing wrong?

Thank you

Sharon Castiglia

Oh and the total was correct without drilling down to the date range.

Paul Newcome

Sharon Castiglia

Thank you so much. I was looking at that formula way too long and couldn't see the obvious. Fresh eyes make all the difference

Paul Newcome

Happy to help. 👍️

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.