How to count how many times a number appears in a spreadsheet? [Solved/Closed]

Report
Posts
3
Registration date
Thursday July 14, 2016
Status
Member
Last seen
July 15, 2016
-
Posts
3
Registration date
Thursday July 14, 2016
Status
Member
Last seen
July 15, 2016
-
Hey~

I need to count the number of times a number appears in a spreadsheet. For example, how many times the number 1 appears in an entire spreadsheet. I have the formula =COUNTIF(B6:M59,"1") but that only tells me how many times only the single digit 1 appears. I need to count how many ones total even if it appears in 11, 16, 21, 51. So in the 5 numbers listed the number 1 appears 5 times. Can someone please help me??

Thanks!
Related:

1 reply

Posts
1837
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 17, 2020
136
kimipapineau, Good evening.

Try it:

MATRICIAL FORMULA
=SUM(LEN(B6:M59)-LEN(SUBSTITUTE(B6:M59,1,"")))


Press SHIFT + CTRL + ENTER at final o formula

Please, tell us if it worked for you.
I hope it helps.
Belo Horizonte, Brasil.
Marcílio Lobão
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
3
Registration date
Thursday July 14, 2016
Status
Member
Last seen
July 15, 2016

Thank you for your response but it didn't work it says #N/A in the cell I put the formula in.
Posts
1837
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 17, 2020
136
kimipapineau, Good morning.

Did you remember that is an array formula?
In this case you need press CTRL+SHIFT+ENTER to enter them into your cell.

Take a look at it and tell us if it's working now.

I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Posts
3
Registration date
Thursday July 14, 2016
Status
Member
Last seen
July 15, 2016
>
Posts
1837
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 17, 2020

YES!! It worked! Thank you so much for your help :)