How to count how many of the same number there is in multiple cells

[pirduh]

I have a sheet where there is from E17 to E32:


12345--

12345-7

12345-7

1-345-7

1234567

123456-

12345-7

1234567

12345--

12345-7

123--6-

12345--

123456-

123-56-

1234567

1234---


And now I have to figure out how many time each number appears.


Numbers from 1 to 7 are in A4:A10.


=COUNTIF($E$17:$E$32,A4) and so on is not working.


Thanks for help!

 

[Excel_Kid_663]

That formula is fine and should work, have you got formulas on to self calculate? Check the bottom left hand corner does it say calculate? if so you need to enable self calculation in your settings. The syntax for count if is as you put it


=COUNTIF(Range,Criteria)


The only other thing I can think of is your formatting is off, format both ranges to be a number and that should work. If not then your count if is correct, there are no matches, check your data

 

[pirduh]

I have tried to change the settings and everything but still not working. Could there be a problem with the formula?

 

[Hui]

Do you want to know how many 1, 2 3 etc appear in the total list E17:E32 ?

 

[pirduh]

yes thats what I want.

 

[NARAYANK991]

Hi ,


Try this :


=SUM(IF(ISNUMBER(FIND(A4,$E$17:$E$32)),1,0))


This is an array formula , to be entered using CTRL SHIFT ENTER. Copy down.


Narayan

 

[Excel_Kid_663]

That will work but then so should the countif, there must be a problem with your data set. Do your cells contain anything other than a number?

 

[pirduh]

yep there are numbers and -

 

[pirduh]

Thanks for your help! Still cant get it right.


This is the full sheet:


Number of flights per weekdays:

1 Monday ?

2 Tuesday ?

3 Wednesday ?

4 Thursday ?

5 Friday ?

6 Saturday ?

7 Sunday ?


Flight Destination Departure Arrival Weekdays

OV 121 Stockholm 07:30 07:30 12345--

OV 123 Stockholm 13:50 13:50 12345-7

OV 125 Stockholm 18:10 18:05 12345-7

OV 137 Oslo 18:30 19:10 1-345-7

OV 141 Copenhagen 06:50 07:25 1234567

OV 143 Copenhagen 13:00 13:35 123456-

OV 145 Copenhagen 18:15 18:50 12345-7

OV 173 Amsterdam 07:35 09:00 1234567

OV 175 Brussels 07:10 08:50 12345--

OV 177 Brussels 18:00 19:35 12345-7

OV 303 Moscow 13:20 16:50 123--6-

OV 311 Kiev 13:20 15:05 12345--

OV 353 Vilnius 13:40 15:05 123456-

OV 473 Amsterdam 17:40 19:05 123-56-

OV 623 St. Petersburg 14:00 17:10 1234567

OV 651 Vilnius 07:35 09:00 1234---

 

[Excel_Kid_663]

Ok I see the problem now. What you need to understand about COUNTIF is that it uses an exact search function. You cant search in a cell containing 1234--- asking for 1 because its looking for an exact match. 1 is not equal to 12345-- therefore the value is false and it returns a 0. You can however get around this one by using what we call a wildcard. A wildcard (which is the * symbol) is a place holder for any number of characters of any value including nothing. Thus allowing you to search for specific text within a cell, however it is subject to text only not numbers. Therefore you will need to format all of your calls to be text. (The ones with an - in are formatted as text already, its the ones without - in) Here is the formula i used, works like a charm


=COUNTIF($E$17:$E$32,"*"&A1&"*")


EDIT


Also just wanted to point out that this will not count more than one per cell.


Example


11--33--55

11--44--66


Of you run the formula with those values in place of your old ones it will return a value of 2, not 4 because this is text remember, you are searching for "anything" & 1 & "anything"


Hope that makes sense

 

[Debraj]

Hi Pirduh,


Check the attached..

=SUMPRODUCT(ISNUMBER(SEARCH(Weeknum,WeeDayRange))*1)

It will search for a WeekNumber (1) and if found.. it will add.. and gives you total found cells count..


https://dl.dropbox.com/u/78831150/Excel/how-to-count-how-many-of-the-same-number-there-is-in-multiple-cells%28Pirduh%29.xlsx


Regards,

Deb

 

[pirduh]

Thank you so much Deb! This was exactly what I was looking for.

 

[Debraj]

Hi Pirduh,


Thanks for your kind words..


By the way.. Above formula is exact what you want.. but if you really wants

how many of the same number there is in multiple cells

then above formula gives you wrong answer..


if in a cells '123156' and you want to check "1" is present or not.. use..

=SUMPRODUCT(ISNUMBER(SEARCH(Weeknum,WeeDayRange))*1)


but if you want to check.. how many times "1" is present.. use below formula..

=SUM(LEN(WeeDayRange)-LEN(SUBSTITUTE(WeeDayRange,Weeknum,"")))

using Ctrl+Shift+Enter


Download the above file again for complete detail..


Regards,

=DEC2HEX(3563)