How to get a countif formula to work when counting a range of a # value

Andrea_Thompson

I am trying to count the # of a certain # in a column. But it gives me either an error message (invalid data type) or a "0". I think I have to use countif and value somehow.

TIA!

Andrée Starå

https://community.smartsheet.com/discussion/109163/how-to-get-a-countif-formula-to-work-when-counting-a-range-of-a-value

Hi,

I hope you're well and safe!

Try something like this. (replace the columns name)

=COUNTIFS(NumbersColumn:NumbersColumn, 10)

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

You shouldn't need a VALUE function. Just the COUNTIF.

=COUNTIF([Column Name]:[Column Name], @cell = #)

Andrée Starå

https://community.smartsheet.com/discussion/109163/how-to-get-a-countif-formula-to-work-when-counting-a-range-of-a-value

Hi,

I hope you're well and safe!

Try something like this. (replace the columns name)

=COUNTIFS(NumbersColumn:NumbersColumn, 10)

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

You shouldn't need a VALUE function. Just the COUNTIF.

=COUNTIF([Column Name]:[Column Name], @cell = #)

Andrea_Thompson

@Paul Newcome you are awesome. I was doing Andree's suggestion above which worked for a straight number, but not when the number is produced by a formula in the column. But yours worked! Thank you.

Paul Newcome

@Andrea_Thompson Happy to help. 👍️

COUNTIF should work on numbers populated by a formula, but it depends on exactly how they are populated and exactly how you are searching for that number. If you put a number in "quotes" it will be considered a text string. Since text and numbers are two different data types, searching for a number in a column populated with text (that just looks like numbers) will result in zero and the other way around.

So if the formula outputting the numbers you want to count has the numbers wrapped in "quotes", your COUNTIF would also need to have the number wrapped in "quotes", and if the formula outputting the numbers does not have "quotes" around them, then you wouldn't use them in the COUNTIF either.

Example:

=IF([Column Name]@row = "Something", "10")

The above will output a text string that just looks like the number 10.

=IF([Column Name]@row = "Something", 10)

The above will output the numerical value of 10.

Your COUNTIF would need to search for either text or numbers (quotes or no quotes) based on the populating formula's output.

Andrea_Thompson

Thank you for the explanation. I had tried both with and without quotations in case that was the issue. I also tried changing the function from the number column between a YEAR( formula and a LEFT( formula that produced the year a different way. But it seems that the = sign was necessary in the countif formula for both cases. Instead of =countif([Year of Issue]:[Year of Issue], 2020) or =countif([Year of Issue]:[Year of Issue], "2020")

This was the one that ultimately worked: =COUNTIF([Year of Issue]:[Year of Issue], =2020)

Paul Newcome

Another difference can come from the YEAR vs LEFT functions.

YEAR outputs numeric date. LEFT outputs text.

Krystal Garcia

@Paul Newcome Hey Paul! I am using your above recommendation but am trying to pull count by a range of numbers.

First column I want to know how many people are between the age of 16-24 for each department. But all are coming up 0?

formula: =COUNTIFS({Hr Headcount Age}, @cell = 16 - 24, {Hr Headcount by line of business}, [DataPoints2]@row)

cross reference 1=

Cross reference 2=

Krystal Garcia

Also @Paul Newcome I updated the formula to correct one for the age range but still coming up with 0

=COUNTIFS({Hr Headcount Age}, AND(@cell >= 16, @cell <= 24), {Hr Headcount by line of business}, [DataPoints2]@row)

Paul Newcome

@Krystal Garcia How exactly is the Age column being populated?