Formula

  • Thread starter Thread starter Harold
  • Start date Start date
H

Harold

I am trying to find a formula to give a collum
of "X"s,"Ph"s,and"*"s,a value of 1 for each,
the collum is L6:L168
The total would show in cell L177
I don't need to know the total of each one, just the grand
total of all three in the collum.

I have tried help, but nothing works.
I have Excel 2000
It would be great if someone could write out the formula
for me.

thank-you
Harold
 
Harold said:
I am trying to find a formula to give a collum
of "X"s,"Ph"s,and"*"s,a value of 1 for each,
the collum is L6:L168
The total would show in cell L177
I don't need to know the total of each one, just the grand
total of all three in the collum.

I have tried help, but nothing works.
I have Excel 2000
It would be great if someone could write out the formula
for me.

thank-you
Harold
Click to expand...

If I understand you correctly, you can just add up three COUNTIFs like this:
=COUNTIF(L6:L168,"X")+COUNTIF(L6:L168,"Ph")+COUNTIF(L6:L168,"~*")
Note that you must use "~*" rather than just "*" in the third, otherwise
that section of the formula will count everything as * is the wildcard
character.

Alternatively, you can use a rather more elegant (but less simple to
understand) approach:
=SUMPRODUCT((L6:L168={"X","Ph","*"})*1)
 
Back
Top