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

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