0

I want to count the C column, whose value is in a list of strings specified in Cell A1 with ',' as the separator, e.g. A1="A,B,C,D", the value can be any of "A", "B", "C" or "D".

Formula is something like this:

B1=COUNTIFS(C:C,Split(A1,","))

I also have A2="1,2,3,4", I want to do the same like:

B2=COUNTIFS(C:C,Split(A2,","))

Sure it can't work, does anyone have an idea to make it work?

ColumnB is the result column, I want to count the occurences of items in ColumnA splitted with ',':

ColumnA        ColumnB       ColumnC
-----------------------------------
1,2,3,4        8              1
1,2            6              1
2,3            2              1
3,4            2              1
                              1
                              2
                              3
                              4
aaron
  • 1,951
  • 3
  • 27
  • 41
  • @pnuts, sorry for unclear description, I updated the question, now is it clearer? thanks. – aaron Jan 23 '13 at 13:56

1 Answers1

0

You can do it using an array formula like so:

{=SUM(IF(NOT(ISERROR(FIND(C:C,A1))),1,0))}

If you have not used an array formula before all you need to do is enter the formula above into a cell but without the curly brackets {} and then press CTRL + SHIFT + ENTER. Just pushing ENTER will not work.

This should also work with numbers providing that the numbers in your A1 cell are in sequential order.

Dave Sexton
  • 10,768
  • 3
  • 42
  • 56