Count multiple values in another Column

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

Hello,

I am hoping someone can give me some help. I am trying to do a count between two lists.

In Sheet1 I have a column with a multiple numbers (as high as 5 and as many as 4) in each cell. On Sheet2 I have an index sheet with another list of numbers in a coulumn. I need a calculation that counts how many numbers in each cell from Sheet1 appear in the column of numbers from sheet2.

For example:

In sheet1 A1 contains 1,3,5

In sheet2 Column A contains 1,1,1,2,3,2,4,5

what I would like is for the calc to count that numbers in sheet1 cell A1 appear 5 times in sheet2 Column A.

Sorry I am not particularly advanced at excel so I hope my explanation makes some sense!

Thanks in advance for any advice

3 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
quotes
In Sheet1 I have a column with a multiple numbers
In sheet1 A1 contains 1,3,5
unquote
this is confusing.
I take it in sheet 1
a1 is 1,A2 is 3 and A3 is 5

and in sheet 2 cells A1 to A7 contains nos 1 1 1 2 3 4 5 respectively

in that case in sheet 2 in B1 (or in any empty cell) copy this formula

=COUNTIF(A1:A7,Sheet1!A1)+COUNTIF(Sheet2!A1:A7,Sheet1!A2)+COUNTIF(Sheet2!A1:A7,Sheet1!A3)

you will get 5



modify this to suit you.
Hi Venkat,

Thanks for your response. I did wonder how clear my explanation was.

Quote
this is confusing.
I take it in sheet 1
a1 is 1,A2 is 3 and A3 is 5

and in sheet 2 cells A1 to A7 contains nos 1 1 1 2 3 4 5 respectively
Unquote

No sorry in sheet1 cell A1 contains 1,3 & 5 in the same cell (then Cell A2 may contain 2 & 3 in the same cell).

and yes in sheet2 cells A1 to A7 contain no. 1 1 1 2 3 4 5 respectively.

I don't know if this is posible but any help or a work around would be great!

Thanks again

Matt
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
one more problem, in A1 do you have 135 or 1,3,5. if you have 135 there will be problem. suppose you have 1329 is it 1,3,29 or 1,3,2,9

if you have 1,3,5 then it is easy because you have to convert it to text to columns by manipulating data-texttocolumns using comma as delimiter . in that case
a1 will become1,b1 will becoming 3, c1 will become 5

and the same formula given earlier with some modification can be used.

what is it. comma is there (or at least space is there)