-1

I want to make an attendance sheet in Excel. If a child is present, the teacher will write P in a cell, or A for absent. I have to add l (which indicates a latecomer) in addition to attendance. So an entry with both P and l must also be counted in total days present; I want to get the number of P and l in a range to calculate total number of days in the month each child was present.

How can I accomplish this?

Improve this question
0

1 Answer 1

Reset to default
0

You can count P with COUNTIF:

=COUNTIF(A:A, "P")

This will give the number of times P appear in column A.

So, if you want to add in those that contain I as well, just add another COUNTIF:

=COUNTIF(A:A, "P") + COUNTIF(A:A, "I")

Change the range as required.

Improve this answer
6
  • Can i give default value of answers to the formula to be blank.Now it is 0 for the cells where we didnt enter data.
    – teacher
    Apr 16, 2014 at 8:35
  • You can add in the cells that are blank as well. =COUNTIF(A:A, "P") + COUNTIF(A:A, "I") + COUNTIF(A:A, "")
    – Jerry
    Apr 16, 2014 at 8:38
  • if i not supply data for an equation(because i gave equation for 50 children but a perticular class have only 45 childern so 5 raws are with out data for equation), the cell with equation in that raw comes 0.I want to change it to nil or leave it blank(No default answer.).Please help me.
    – teacher
    Apr 16, 2014 at 8:49
  • @teacher Well, you would use COUNTIFS for this, but on another hand, the formula will be three times longer. You might merge them with SUMPRODUCT. If say, the names are in column A, the attendance in column B, then you could use: =SUMPRODUCT((A:A<>"")*(B:B={"P","I",""}))
    – Jerry
    Apr 16, 2014 at 14:15
  • but i already have a formula in column of total attendance.in that column only 0 comes.i want to make that colum appear blank.
    – teacher
    Apr 17, 2014 at 8:42

Not the answer you're looking for? Browse other questions tagged .