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
-
kimipapineau
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!

1 reply

Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
134
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 4380 users have said thank you to us this month

kimipapineau
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.
Mazzaropi
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
134
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
kimipapineau
Posts
3
Registration date
Thursday July 14, 2016
Status
Member
Last seen
July 15, 2016
> Mazzaropi
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018

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