Need to look for $99,000-$249,000 in Metrics Sheet. Can't find formula error.
=COUNTIFS({1. Case Queue Range 6},>"$99,000",<="$249,000")
I figured it out thanks.
=AVG(COLLECT({Case Consultation Tracker Range 99}, {Case Consultation Tracker Range 99}, >=100000 < 500000, {Case Consultation Tracker 98}, >=DATE(2024, 1, 1)))
@Genevieve P. When did Smartsheet start allowing that syntax??
Your syntax is off. Try this instead:
=COUNTIFS({1. Case Queue Range 6},AND(@cell>99000,@cell<=249000))
Thanks this works. =SUMIF({1. Case Queue Investable Assets}, >99999 <= 499999
NOw I have two more I'm trying to get to work:
Thanks again Lisa
Sorry one more: We have a field that contains several values from a check list. We want to count one of the values (e.g. Estate Planning) in each field that has it. My formula is only picking up the ones in the column by itself. Do I have to use a "Contains" and how do I do that please? ❤️My attempt:
=COUNTIF({1. Case Queue Topics}, CONTAINS["College/Education"])
or =COUNTIF({1. Case Queue Topics}, CONTAINS("College/Education"))
or
Hi @lisalettieri
With a COUNTIFS, you'll want to list the {cross sheet range}, then have a comma, then the criteria. [these square] brackets are only for in-sheet column name references.
So for:
=COUNTIFS({New Project Tracker 2024 Status}, "Complete", {New Content Tracker 2024 Format}, "Webinar")
And then for your second comment, if you're using a multi-select column use HAS instead.
=COUNTIF({1. Case Queue Topics}, HAS(@cell, "College/Education"))
This says to look in the cell to see if it has the selection "xyz" along with other selections.
For your second formula, "Projects completed each month", the data in the source sheet would need to be in a date column for it to easily count the months. If it's numerical, I would suggest having a helper column in your source sheet that extracts the number in between your / and /. Then you can use that helper column in your other COUNT formulas.
In order to know how to build the formula to extract the month, we'd need to know if the way you're typing it in is standardized. For example, do you always have 10 characters: DD/MM/YYYY
Or is your 1/1/2024 showing that the month is the first value?
Cheers,
Genevieve
Hey @lisalettieri & @Paul Newcome
So that Syntax actually isn't "allowed" - or it won't function properly. I tested it and the COLLECT function skips over the criteria… meaning it will create an Average and give an output, but the filter won't properly work.
Here's an example source sheet:
Here's the example output:
Notice that the one where >=1 <50 is used, the average is of the whole column without the filter.
@lisalettieri try this instead:
=AVG(COLLECT({Case Consultation Tracker Range 99}, {Case Consultation Tracker Range 99}, AND(@cell >=100000, @cell < 500000), {Case Consultation Tracker 98}, >=DATE(2024, 1, 1)))
Cheers, Genevieve
I also tested it, and it worked for me though. In the first screenshot, it filtered out the 11 because the [Letter] column has a "b" in it, but the second screenshot has it included after I changed the letter to an "a".
The only difference is that I am on same sheet instead of cross sheet references.
Hey @Paul Newcome
I believe it's working because of the Letter filter, not the number filter 🙂 Right now your number range is including all values.
Try adjusting your number filter and skip the letters.
=AVG(COLLECT(Number:Number, Number:Number, >=1 <10))
My guess here is that you will still get 3, even though you should no longer have 11 as part of your criteria.
This is the syntax I would recommend:
=AVG(COLLECT(Number:Number, Number:Number, AND(@cell >=1, @cell <10)))
It still excluded a number that was outside of my range (formula in [Column19])…
=AVG(COLLECT(Number:Number, Number:Number, >=1 < 12))
(but I still prefer the AND syntax)
Interesting! It's the 0 that it cannot filter out correctly:
I just tested some more. I think the best way to explain it is that it ignores the first argument and only filters based on the second. I changed the criteria to
>= 5 < 12
And it pulled in everything less than 12 including those numbers that were not greater than 5.
These worked for referring to another sheet:
=COUNTIFS({Case Consultation Tracker Investable Assets}, >=500000 < 1000000, {Case Consultation Tracker Submission Date}, >=DATE(2024, 1, 1), {Case Consultation Tracker Range Status}, ="Active")
=MEDIAN(COLLECT({Case Consultation Tracker Investable Assets}, {Case Consultation Tracker Investable Assets}, >=500000 < 1000000, {Case Consultation Tracker Submission Date}, >=DATE(2024, 1, 1)))
is returning a number below 500,000!
@lisalettieri Right. Using that syntax, the formula ignores the the first argument. In your particular case, it is ignoring the ">= 500000" portion and only pulling in rows that meet the "< 1000000" criteria. You need to use the Syntax @Genevieve P. previously suggested with the AND function.
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.
