Counting Uppercase and Lowercase separately

[Carolyn]

I'm using the following formula:
=COUNTIF(A1:A4,"T")+(COUNTIF(A1:A4,"t")/2)
but Excel isn't noting the difference between upper and lower case
entries. Is there another way to do this?

A1 = T
A2 = T
A3 = t
A4 = t

I want to count these items as if uppercase letters equaled 1 and
lowercase letters equaled 0.5.

Thanks.

 

[Domenic]

To count upper case T...

=SUMPRODUCT(--(ISNUMBER(FIND("T",A1:A4))))

For lower case T, change the "T" to a "t".

Hope this helps!

 

[Ron Rosenfeld]

I'm using the following formula:
=COUNTIF(A1:A4,"T")+(COUNTIF(A1:A4,"t")/2)
but Excel isn't noting the difference between upper and lower case
entries. Is there another way to do this?

A1 = T
A2 = T
A3 = t
A4 = t

I want to count these items as if uppercase letters equaled 1 and
lowercase letters equaled 0.5.

Thanks.

The *array* formula:

=SUM(--EXACT(A1:A10,"T"))+
SUM(--EXACT(A1:A10,"t"))/2

will do what you want.

To enter an *array* formula, after typing or pasting in the formula, hold down
<ctrl><shift> while hitting <enter>.

XL will place braces {...} around the formula.


--ron

 

[Carolyn]

Thank you Ron Rosenfeld and Domenic who answered me from excelforum.
Both suggestions work wonderfully!
____________________________________________________

The *array* formula:

=SUM(--EXACT(M5:AU5,"T"))+SUM(--EXACT(M5:AU5,"t"))/2

will do what you want.

To enter an *array* formula, after typing or pasting in the formula, hold down
<ctrl><shift> while hitting <enter>.

XL will place braces {...} around the formula.
____________________________________________________

To count upper case T...

=SUMPRODUCT(--(ISNUMBER(FIND("T",A1:A4))))

For lower case T, change the "T" to a "t".

Hope this helps!

 

[Ron Rosenfeld]

Thank you Ron Rosenfeld and Domenic who answered me from excelforum.
Both suggestions work wonderfully!
____________________________________________________

The *array* formula:

=SUM(--EXACT(M5:AU5,"T"))+SUM(--EXACT(M5:AU5,"t"))/2

will do what you want.

To enter an *array* formula, after typing or pasting in the formula, hold down
<ctrl><shift> while hitting <enter>.

XL will place braces {...} around the formula.
____________________________________________________

To count upper case T...

=SUMPRODUCT(--(ISNUMBER(FIND("T",A1:A4))))

For lower case T, change the "T" to a "t".

Hope this helps!

You're welcome. Thank you for the feedback.


--ron