Counting Upper case P as 1 and lower case p as 0.5

[gamurphy64]

How can I count an upper case "P" as a 1.0 and a lower case "p" as 0.5 in the same row. I'm using =CountIF and body have a formula for me to try.

Thanks for you help.
Greg

[Richard Buttrey]

Hi,

This array formula is one way of counting the number of upper case Ps in a range. Enter it with Ctrl-Shift-Enter

Please Login or Register  to view this content.

replace the 80 with 112 for lower case p.

Not sure whether having found the numbers you want to do something else like perhaps multiply the count by 1 in the case of P and 0.5 if p. In which case just add *1 or *.5

[gamurphy64]

Yes want to count the Upper case Ps and Lower case p's and add them together.

[satabp]

Hi,

Try this array formula...

Please Login or Register  to view this content.

As Richard said, Enter it with Ctrl-Shift-Enter

[Richard Buttrey]

In that case use the formulae I gave you and add them after multiplying each one by its relevant factor.

[gamurphy64]

Sorry guys I'm not getting it. I have attached an example of the data and formula below with my cell range.

=(SUM(--IF(I82:JJ82<>"",CODE(I82:JJ82)=112,0))/2)+(SUM(--IF(I82:JJ82<>"",CODE(I82:JJ82)=80,0)))

[satabp]

Once you enter the formula, you need to press Ctrl+Shift+Enter, then you will get the result.

If you already enter then press F2 then press Ctrl+Shift+Enter

[bebo021999]

Why didn't we use FIND for upper and lower case:

=SUMPRODUCT(NOT(ISERROR(FIND("P",$I$82:$JJ$82)))+NOT(ISERROR(FIND("p",$I$82:$JJ$82)))/2)

Enter only.

[Richard Buttrey]

Why didn't we use FIND for upper and lower case:

No particular reason. That's the beauty of Excel. There are often several ways of achieving the same result

[gamurphy64]

Wow that is a beautiful formula! Works perfectly! Exactly what I needed.

Thank you!
Greg

[daddylonglegs]

For exact case matches perhaps use EXACT function, e.g. this formula should give the same result. The first part counts all "P"s and the EXACT part adds another 1/2 for each upper case "P"

=SUMPRODUCT((I82:JJ82="P")+EXACT(I82:JJ82,"P"))/2

[shg]

Nice, DLL.