Couting numbers inside one cell or a range of cells

[PartisanStar]

Let's say my A1 cell looks like this: 1, 1, 1, 1, 1, 5, 3, 1, 1
My A2 cell looks like this: 1, 2, 1, 1

The numbers are separated by a comma.

I want to count how many times a number 1 appears in a cell A1 (7) and in a range of cells A1:A2 (10).

Can you help me out?

 

[PartisanStar]

Hi,

Assuming that the values within your strings are always separated by the precise combination of a single comma followed by a single space (as in the examples you give) and that you put e.g. 1 (the value for which you wish to perform the count) in D1, these are your two required formulas:

=(LEN(" "&A1&", ")-LEN(SUBSTITUTE(" "&A1&", "," "&$D$1&",","")))/LEN(" "&$D$1&",")

=SUMPRODUCT(LEN(" "&A1:A2&", ")-LEN(SUBSTITUTE(" "&A1:A2&", "," "&$D$1&",","")))/LEN(" "&$D$1&",")

Regards

I'm pretty sure this is a genius solution but why doesn't it work for me? Perhaps the function is written for a different version of Excel? I'm using 2007 version. Can you help me out?

I have about 600 rows to count for certain numbers so you will save me a tons of work and will for sure go to heaven for this. :D

 

[Rick Rothstein]

I'm pretty sure this is a genius solution but why doesn't it work for me? Perhaps the function is written for a different version of Excel? I'm using 2007 version. Can you help me out?

I have about 600 rows to count for certain numbers so you will save me a tons of work and will for sure go to heaven for this. :D

Show us the exact formulas you used on your worksheet that did not work for you.

 

[PartisanStar]

Show us the exact formulas you used on your worksheet that did not work for you.

Hello. I think I did what I was told to do. I copy-pasted a formula and put a 1 in D1. The A1 and A2 cells are like I written before.

Can someone perhaps upload a simple Excel document that demonstrated these two functions at work?

 

[PartisanStar]

Hello guys.

I've decided to simlify my woksheet a little bit since there are too many errors in it currently for any compliated function to work properly.

My table will now look something like this:

Example.

A column can only include numbers from 1 to 3 and each cell only 1 number.

B column can include numbers from 1 to 12 and each cell only one number.

What I need to find out is for example how many times occurs that if there is a number 1 in a column A, there is also a number 1 in a corresponding column B.

In example above this occurs 2 times: in row 1 and 3.

 

[Rick Rothstein]

Hello guys.

I've decided to simlify my woksheet a little bit since there are too many errors in it currently for any compliated function to work properly.

My table will now look something like this:

Example.

That data layout is completely different than what the previously submitted formulas were designed for. Can you explain what result or results you are looking for from this new data arrangement?

 

[PartisanStar]

That data layout is completely different than what the previously submitted formulas were designed for. Can you explain what result or results you are looking for from this new data arrangement?

Hello Rick. Yes it is, I decided to simplify it.

I think you've just missed my expected results since I edited the post and you posted the same minute. Check my previous post again, please.

I might add that the number I'll be looking for won't always be the same in both columns.

For example, I might want to find out how many times occurs that there is a number 2 in a column A and number 11 in a corresponding column (the same row) B, or a number 1 in column A and number 6 in column B etc. etc.

 

[AndyPandy27]

Hi PartisanStar,

You should be able to achieve what you are after using COUNTIFS (Excel 2007 or later only).

Using your above example, in cell C1 type: =COUNTIFS($A$1:$A$10,1,$B$1:$B$10,1)

This would actually result in 3 (row 1, row 3 and row 9).

Hope this helps,

AP

 

[Arithos]

@PartisanStar

You are replacing "," with ";" if you require that as a "divider" in your formulas?

The first formula (the ingenious one) would then be like this:
=SUMPRODUCT(LEN(" "&A1:A2&", ")-LEN(SUBSTITUTE(" "&A1:A2&", ";" "&$D$1&",";"")))/LEN(" "&$D$1&",")

I need to do this to make my formulas work, (EU) user <--

 

[PartisanStar]

AndyPandy27, thanks, works great!

Arithos, you're right and thanks for clearing that out for me! Now it works like it should!

 

[AndyPandy27]

No problem - glad to be of help.

Cheers,

AP