Totalling letters (Excel 2000/SP3)

Topic

New Reply

I have a column of 40 rows. Each cell in the column has an entry of either 1, A, P or X. In the same column in row 41 I have a SUM formula from row 1 to row 40 that adds & displays the number of cells with the entries 1 & so far I don’t have a problem as the letters are ignored.

But in row 42 I want to display the total number of cells with the entries of both P & A in them. And in row 43 I want to display the number of cells with X in them.

Can this be done & if so how?

Malcolm Walker

Reply | Quote

Replies

If each cell has an entry of EITHER 1, A, P or X, there are no cells that contain BOTH P and A. I assume that you mean cells that contain either P or A. Assuming that your data are in column A, the formula for this is

=COUNTIF(A1:A40,”P”)+COUNTIF(A1:A40,”A”)

and the formula to count the number of cells with an X is

=COUNTIF(A1:A40,”X”)

Reply | Quote

Hans,

A belated but sincere thank you.

Malcolm

Reply | Quote

Hans,

A belated but sincere thank you.

Malcolm

Reply | Quote

If each cell has an entry of EITHER 1, A, P or X, there are no cells that contain BOTH P and A. I assume that you mean cells that contain either P or A. Assuming that your data are in column A, the formula for this is

=COUNTIF(A1:A40,”P”)+COUNTIF(A1:A40,”A”)

and the formula to count the number of cells with an X is

=COUNTIF(A1:A40,”X”)

Reply | Quote