Countifs & HAS referencing a separate sheet

Hi there, would really appreciate some help with this.

I have 2 sheets created; 1) Deployments Complete and 2) Monthly Billing.

Sheet 1 is data that is updated daily on my deployment project.
In sheet 1 I have a Comments column that is text format.
Sheet 2 is where I summarise the data from sheet 1 and report from these numbers.
Sheet 2 references various ranges in sheet 1.

Objective;

In Sheet 2, I want to count the number of times the phrase “Device Deployed at Chambers” is used in a calendar month in the Comments column in Sheet 1.

I looked at using Contains, Find and Has and each has slightly different functions so I decided on HAS and the formula I have used is;

=COUNTIFS({Deployments Complete Range 1}, >=[Month Start]1, {Deployments Complete Range 1}, <=[Month End]1, {Deployments Complete Range 2}, HAS(@cell, "Device Deployed at Chambers"))

The formula above is used in Sheet 2. Range 1 is in Sheet 1 and is a date column and Range 2 is in Sheet 1 and is a text column. The issue is the formula is pulling back a zero answer when it should be 112. Where am I going wrong ?

Thanks

Steve

Find more posts tagged with

Accepted answers

All comments

David Tutwiler

How is your Month Start field formatted? You could be comparing two different types of data. If the Range 1 is bringing back a date and your Month Start is referencing a month, it would not be comparable and you'd never get to the HAS part of the COUNTIFS function because the date compares would all fail.

If this is indeed the case, you could wrap the Range 1 in a MONTH() function to return the month number from the date. For instance if the date was January 1st, 2021 you'd get a 1 from the MONTH() function.

Steve Woolley

Hi David,

Many thanks for the prompt response. The Month Start and Month End in sheet 2 are both Date formatted columns and so is Range 1 in sheet 1.

Hopefully the images below will show what i am trying to achieve.

This is sheet 2 that references sheet 1.The formula i have used is in the Chambers column. with a zero value.

The source data (sheet 1) is in the image below. Where Date Deployed is a date format and is Range 1 and the Problem/Issues column is text format and is Range 2.

Many thanks in advance for the help on this.

Steve

David Tutwiler

I don't see anything wrong with your formula. The only things I could suggest would be nitpicky, but I don't think they would cause the function to not work. I'm sorry.

Could you try to use the @row syntax within your COUNTIFS rather than [Month Start]1 format? Also, if you have standard answers in your Problems/Issues column, it may make sense to convert that column into a MultiSelect Dropdown so that you don't have to rely on typed text and might get more standard input.

Again, I'm not sure if either of these things would fix your issue, but I don't see a problem with your formula you have as written. The only other thing I could think of would be to redefine your ranges just to make sure they are right. I've had issues with named ranges before that fixed themselves when I re-identified them under a new name.

Steve Woolley

Hi David,

Again thanks for the help and the prompt response.

Strangely enough i had tried the things you mention but was still getting the zero return so thought i would put it out there to the community, thinking i must be missing something obvious. Having been a Smartsheet user for about 3 years now i couldn't see anything wrong with my formula and set up either.

The @row is what i started with but then 'defined' the cell just to make sure. The comments box has so many variables unfortunately i have to leave it as free text.The ranges i have 'refreshed' using the same name but i will try redefining them later today and see if that makes any difference.

Thanks for the help and input, really appreciated.

Steve

David Tutwiler

No problem. If the issue persists I would recommend scheduling time with the Pro Desk or calling support on their 24-hour line, if your plan supports it.

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.