Need to look for $99,000-$249,000 in Metrics Sheet. Can't find formula error

lisalettieri

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")

lisalettieri

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)))

Paul Newcome

@Genevieve P. When did Smartsheet start allowing that syntax??

=AVG(COLLECT({Case Consultation Tracker Range 99}, {Case Consultation Tracker Range 99}, >=100000 < 500000, {Case Consultation Tracker 98}, >=DATE(2024, 1, 1)))

Paul Newcome

Your syntax is off. Try this instead:

=COUNTIFS({1. Case Queue Range 6},AND(@cell>99000,@cell<=249000))

lisalettieri

Thanks this works. =SUMIF({1. Case Queue Investable Assets}, >99999 <= 499999

NOw I have two more I'm trying to get to work:

1. Countif in column Format ="Webinar" and Status="Complete" and it won't:=COUNTIFS({New Project Tracker 2024 Status}, ="Complete", [{New Content Tracker 2024 Format} ="Webinar"])
2. Another is Projects completed each month. The data is in numerical form as in 1/1/2024. Don't know where to begin. We just want to Count each in Jan, each in Feb. etc. Do I have to specify a date range for each in my metrics sheet? Ugh.

Thanks again Lisa

lisalettieri

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

Genevieve P.

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:

1. Countif in column Format ="Webinar" and Status="Complete" 

=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

lisalettieri

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)))

Paul Newcome

@Genevieve P. When did Smartsheet start allowing that syntax??

=AVG(COLLECT({Case Consultation Tracker Range 99}, {Case Consultation Tracker Range 99}, >=100000 < 500000, {Case Consultation Tracker 98}, >=DATE(2024, 1, 1)))

Genevieve P.

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

Paul Newcome

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.

Genevieve P.

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)))

Paul Newcome

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)

Genevieve P.

Interesting! It's the 0 that it cannot filter out correctly:

Paul Newcome

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.

lisalettieri

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!

Paul Newcome

@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.