=COUNTA help

[Scottsdale]

I have 6 cells using columns (K2 to P2) that may contain a number followed by a one letter. Eg. 3G or maybe 16B. This number/letter is a child's age and gender: G = girl, B = boy. I need one formula to count the cells if the number is less than 13 and another formula to count the cells if the number is greater than 12.

I've tried using these COUNTA formulas:
=COUNTA(K2:P2;"<13")
=COUNTA(K2:P2;">12")
...but if 3 cells contain data, it returns a count of 4 cells, which is wrong.

Any suggestions? Splitting the age/gender into two columns (though easy) is not an option.

Cheers,
Scott

 

[Accelerate Excel]

Hi,

You can either try using a customer format where G & B are just formatted i.e it stays as a number.

However, if you want to have it as a string I would simply add a helper column for the count using:

=IFERROR(VALUE(LEFT(K1,LEN(K1)-1)),"n.a.")

Then use the counts:
Bigger equal 13:

=COUNTIFS(K2:P2,">=13")

Less then 13:

=COUNTIFS(K2:P2,"<13")

 

[RajeshKumar1313]

Hi Scottsdale,
use this one after entering just press
CTRL+SHIFT+ENTER

=SUM(IF(ISNUMBER(VALUE(LEFT(K2:P2,2))),IF(VALUE(LEFT(K2:P2,2))<13,1,0),IF(VALUE(LEFT(K2:P2,1))<13,1,0)))
=SUM(IF(ISNUMBER(VALUE(LEFT(K2:P2,2))),IF(VALUE(LEFT(K2:P2,2))>12,1,0),IF(VALUE(LEFT(K2:P2,1))>12,1,0)))

 

[Scottsdale]

Hi,

You can either try using a customer format where G & B are just formatted i.e it stays as a number.

However, if you want to have it as a string I would simply add a helper column for the count using:

=IFERROR(VALUE(LEFT(K1,LEN(K1)-1)),"n.a.")

Then use the counts:
Bigger equal 13:

=COUNTIFS(K2:P2,">=13")

Less then 13:

=COUNTIFS(K2:P2,"<13")

View attachment 115807

Hi....this worked! Cheers for your quick reply!

 

[Scottsdale]

Hi Scottsdale,
use this one after entering just press
CTRL+SHIFT+ENTER

=SUM(IF(ISNUMBER(VALUE(LEFT(K2:P2,2))),IF(VALUE(LEFT(K2:P2,2))<13,1,0),IF(VALUE(LEFT(K2:P2,1))<13,1,0)))
=SUM(IF(ISNUMBER(VALUE(LEFT(K2:P2,2))),IF(VALUE(LEFT(K2:P2,2))>12,1,0),IF(VALUE(LEFT(K2:P2,1))>12,1,0)))

Hi...thx for your reply too! Worked well. Cheers.

 

[Peter_SSs]

Hi...thx for your reply too! Worked well. Cheers.

I have removed the 'Mark as solution' from that post as it isn't the solution.
From your comments it appears there may be two posts with solutions. If that is the case then you can mark one of those with the 'Mark as solution' (preferred) or else leave the thread without such a mark (less preferred if there is actually a solution).