sum in columns

[K11ngy]

I have a register where I have to Insert a "1" if student is present and this
adds up at bottom of column as usual.

If student is late I want to put "L" in the cell but I also want Excel to
add these along with the number "1" as well?#

Any ideas

Thanks

Steve King

 

[BartParry]

I have a register where I have to Insert a "1" if student is present and this
adds up at bottom of column as usual.

If student is late I want to put "L" in the cell but I also want Excel to
add these along with the number "1" as well?#

Any ideas

Thanks

Steve King

counta should do the trick.

 

[PCLIVE]

This should do what you want.

=COUNTIF(A:A,1)+COUNTIF(A:A,"L")

HTH,
Paul

 

[Dave Peterson]

Another one:
=SUM(COUNTIF(A:A,{1,"L"}))

Although, I bet I'd use PCLive's.

 

[BartParry]

Another one:
=SUM(COUNTIF(A:A,{1,"L"}))

Although, I bet I'd use PCLive's.

counta's simplier.

 

[Dave Peterson]

And if I have other codes in that range????

counta's simplier.

 

[PCLIVE]

COUNTA counts non-blank cells and will not produce accurate results if there
are other cells in the range that contain data other than 1's or L's, .

 

[BartParry]

COUNTA counts non-blank cells and will not produce accurate results if there
are other cells in the range that contain data other than 1's or L's, .

that wasn't in the specs.

 

[Peo Sjoblom]

that wasn't in the specs.

Only the OP can tell but I expect that he might put a zero for a student who
is not present and that would make your formula incorrect whereas the 2
other solutions will work regardless.

 

[PCLIVE]

Sorry, but I have to disagree. The OP clearly stated he wanted to add up
the number of ones and "L"s in a range. He did not say there would or could
be anything else in the range (such as headings, subheadings, etc). So we
have to assume. Using the COUNTA function can produce the wrong results if
some of these other factors exist. But by using the COUNTIF function to
count exactly what you are looking for, you reduce the possibility of
incorrect results.