Previous Topic Next Topic

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

Solved/Closed
kimipapineau Posts 3 Registration date Thursday July 14, 2016 Status Member Last seen July 15, 2016 - Jul 14, 2016 at 03:44 PM
kimipapineau Posts 3 Registration date Thursday July 14, 2016 Status Member Last seen July 15, 2016 - Jul 15, 2016 at 11:20 AM
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 response

Answer 1 / 1
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Updated on Dec 12, 2018 at 12:43 PM
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.
2
kimipapineau Posts 3 Registration date Thursday July 14, 2016 Status Member Last seen July 15, 2016
Jul 15, 2016 at 09:18 AM
Thank you for your response but it didn't work it says #N/A in the cell I put the formula in.
0
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Updated on Dec 12, 2018 at 12:44 PM
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
1
kimipapineau Posts 3 Registration date Thursday July 14, 2016 Status Member Last seen July 15, 2016 > Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023
Jul 15, 2016 at 11:20 AM
YES!! It worked! Thank you so much for your help :)
0

Similar discussions

You have guessed the code too many times. Contact support
nanosama95 -
Komu -

4 responses
Facebook says I have been trying too often
Sameer -
owilson -

2 responses
Counting cells containing only numbers
jutulu -
jutulu -

4 responses
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
Counting number of times a value appears if..
jensorr -
sambr22 -

7 responses