Report

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

Ask a question kimipapineau 3Posts Thursday July 14, 2016Registration date July 15, 2016 Last seen - Latest answer on Jul 15, 2016 11:20AM
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!
See more 
Helpful
+0
moins plus
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.
kimipapineau 3Posts Thursday July 14, 2016Registration date July 15, 2016 Last seen - Jul 15, 2016 09:18AM
Thank you for your response but it didn't work it says #N/A in the cell I put the formula in.
Reply
Add comment
Helpful
+0
moins plus
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.

I did a little example for you using my suggested formula.
http://speedy.sh/VhX2h/14-07-2016-Counting-Numerals-OK.xlsx

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 3Posts Thursday July 14, 2016Registration date July 15, 2016 Last seen - Jul 15, 2016 11:20AM
YES!! It worked! Thank you so much for your help :)
Reply
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!