0

I have a column of data show as below. I would like to count the number of items that contain "JJ number", and "JJ alphabet", which are 6 and 4. I try to use countifs function with the first criteria as " JJ *`` " but then I fail to get the second criteria right by using left, len or isnumber function.

This is the partial code that I have been working on:

=COUNTIFS(A:A, "JJ *", ...

Here is an example of input data:

jj 1288
jj 2838
jj 3344
jj 6288
jj 8838
jj 3344
jj fan
jj tsz
jj zch
J. Chomel
  • 8,193
  • 15
  • 41
  • 69
YS Cheung
  • 9
  • 5
  • could u add a extra coulumn? That way u could split the cell and make it easier. – krib Sep 14 '16 at 09:36
  • The data come in this way, so it's difficult to split them apart. I don't have problem setting two formula for JJ number and JJ alphabet though. – YS Cheung Sep 14 '16 at 10:18

2 Answers2

0

Well here is a table I created which will solve your problem. I took it step by step in the below screenshot but here is one formula which can help you.

Add 2 columns to the right of your table and add the below formula

=IF(ISNUMBER(IFERROR(INT(RIGHT([@colName],LEN([@colName])-SEARCH(" ",[@colName]))),"")),1,0)

This will return 1 if it is a number and 0 if it is a charecter. In the next column use the below formula.

=IF([@newcolumn]=1,0,1)

Change the @colname & @newcolumn to the column name you keep in the table. @colname is the first column and @newcolumn is the column we added with the formula.

Once you achieve this, a simple sum will get you the result.

enter image description here

SS97
  • 115
  • 1
  • 14
  • Hi, thank you for the detailed explanation for the problem, but I am confused by colName and newcolumn. Should I replace colName with A:A and newcolumn with C:C? Assume that A:A is where the original data located, the first formula you provided will place on column B and the second will place on column C. – YS Cheung Sep 14 '16 at 11:04
  • Make a table "Cntrl + T", "colname" & "newcolumn" are the headers of the columns.. You can change "colname" or " newcolumn" to cell A1 etc. Like in my above image, you see the table headers as "name","space","after space" etc. – SS97 Sep 14 '16 at 12:57
  • Good to know that! Cheers! – SS97 Sep 15 '16 at 04:23
0

My initial idea was to write

 =IFERROR(NUMBERVALUE(MID(A1;4;20));"text")

If you put that in the a column you would get the number as a value and the cells with JJ text would output text. After that you can use your COUNTIF

krib
  • 569
  • 4
  • 14
  • Thanks for your idea, however, when I put the formula into work, the result come out as "text" regardless the referencing cell is `JJ Number` or `JJ text` this is how my excel 2010 accepts it `=IFERROR(NUMBERVALUE(MID(A1,4,20)), "text")` – YS Cheung Sep 14 '16 at 14:40