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
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!
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:
- How to count how many times a number appears in excel
- Number to word in excel - Guide
- How to count names in excel - Guide
- How to change time in whatsapp - Guide
- How to change date format in excel - Guide
- How to open excel in notepad - Guide
1 response
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
Updated on Dec 12, 2018 at 12:43 PM
kimipapineau, Good evening.
Try it:
MATRICIAL FORMULA
Press SHIFT + CTRL + ENTER at final o formula
Please, tell us if it worked for you.
I hope it helps.
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.
Jul 15, 2016 at 09:18 AM
Updated on Dec 12, 2018 at 12:44 PM
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
Jul 15, 2016 at 11:20 AM