BE A WHIZ!!! Add specified text cells to equal "1" in last column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to make an attendace sheet and I would like to add up the absences ONLY (only the columns (cells) with an "A" in them, other columns may contain a "P".) I can add up all of the "A" columns, however, the number in the "TOTAL" column equals however many "A"s are actually in the other columns before it. I need the number in the total column to equal "1". For example, there are 4 classes in one day. If a student is absent for 2 classes, they are marked absent for the enitre day, but the columns would read A, A, P, P. ("A"=absent, "P"=present). I need to add only the columns that actually have an A in them and then get the "TOTAL" column to equal "1" so that the workshhet reads that they were absent for 1 day. If I have 4 teachers who mark them absent for all 4 classes, I need Excel to add up all of the "A"s and still put the number "1" in the "TOTAL" column so that they are marked absent for the day. Now, if the students are marked absent for 1 class and were present in the other 3, they would not be marked absent for the entire day, so the "TOTAL" column should read "0". This total column with the "1"s in it will then be totalled for the week, the month, and the entire course on a regular basis. I think there may be a simple explanation, but I can't figure it out! PLEASE HELP!!!!!!!
 
Try something like:

=MIN(1,COUNTIF(A1:D1,"A"))

--

Vasant



KJParenti said:
I need to make an attendace sheet and I would like to add up the absences
Click to expand...
ONLY (only the columns (cells) with an "A" in them, other columns may
contain a "P".) I can add up all of the "A" columns, however, the number in
the "TOTAL" column equals however many "A"s are actually in the other
columns before it. I need the number in the total column to equal "1". For
example, there are 4 classes in one day. If a student is absent for 2
classes, they are marked absent for the enitre day, but the columns would
read A, A, P, P. ("A"=absent, "P"=present). I need to add only the columns
that actually have an A in them and then get the "TOTAL" column to equal "1"
so that the workshhet reads that they were absent for 1 day. If I have 4
teachers who mark them absent for all 4 classes, I need Excel to add up all
of the "A"s and still put the number "1" in the "TOTAL" column so that they
are marked absent for the day. Now, if the students are marked absent for 1
class and were present in the other 3, they would not be marked absent for
the entire day, so the "TOTAL" column should read "0". This total column
with the "1"s in it will then be totalled for the week, the month, and the
entire course on a regular basis. I think there may be a simple explanation,
but I can't figure it out! PLEASE HELP!!!!!!!
 
-----Original Message-----
I need to make an attendace sheet and I would like to
Click to expand...
add up the absences ONLY (only the columns (cells) with
an "A" in them, other columns may contain a "P".) I can
add up all of the "A" columns, however, the number in
the "TOTAL" column equals however many "A"s are actually
in the other columns before it. I need the number in the
total column to equal "1". For example, there are 4
classes in one day. If a student is absent for 2 classes,
they are marked absent for the enitre day, but the
columns would read A, A, P, P.
("A"=absent, "P"=present). I need to add only the
columns that actually have an A in them and then get
the "TOTAL" column to equal "1" so that the workshhet
reads that they were absent for 1 day. If I have 4
teachers who mark them absent for all 4 classes, I need
Excel to add up all of the "A"s and still put the
number "1" in the "TOTAL" column so that they are marked
absent for the day. Now, if the students are marked
absent for 1 class and were present in the other 3, they
would not be marked absent for the entire day, so
the "TOTAL" column should read "0". This total column
with the "1"s in it will then be totalled for the week,
the month, and the entire course on a regular basis. I
think there may be a simple explanation, but I can't
figure it out! PLEASE HELP!!!!!!!
.
try this formula in the total column
Click to expand...
=IF(COUNTIF(E3:E6,"a")>1,1,0) this will count the "a" in
your range e3:e6 and if they are greater than 1 will
return a 1 in the total column if not they will return a 0
Good luck!
 
I believe the others who responded missed the latter part of your long
explanation. Of course, better to write more than less...

Use the untested =IF(COUNTIF(cells-of-interest,"A")>=2,1,0)

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

I need to make an attendace sheet and I would like to add up the absences ONLY (only the columns (cells) with an "A" in them, other columns may contain a "P".) I can add up all of the "A" columns, however, the number in the "TOTAL" column equals however many "A"s are actually in the other columns before it. I need the number in the total column to equal "1". For example, there are 4 classes in one day. If a
Click to expand...
student is absent for 2 classes, they are marked absent for the enitre day, but the columns would read A, A, P, P. ("A"=absent, "P"=present). I need to add only the columns that actually have an A in them and then get the "TOTAL" column to equal "1" so that the workshhet reads that they were absent for 1 day. If I have 4 teachers who mark them absent for all 4 classes, I need Excel to add up all of the "A"s and
still put the number "1" in the "TOTAL" column so that they are marked absent for the day. Now, if the students are marked absent for 1 class and were present in the other 3, they would not be marked absent for the entire day, so the "TOTAL" column should read "0". This total column with the "1"s in it will then be totalled for the week, the month, and the entire course on a regular basis. I think there may be a
simple explanation, but I can't figure it out! PLEASE HELP!!!!!!!
 
Good catch, Tushar!

--

Vasant

Tushar Mehta said:
I believe the others who responded missed the latter part of your long
explanation. Of course, better to write more than less...

Use the untested =IF(COUNTIF(cells-of-interest,"A")>=2,1,0)

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
Click to expand...
absences ONLY (only the columns (cells) with an "A" in them, other columns
may contain a "P".) I can add up all of the "A" columns, however, the number
in the "TOTAL" column equals however many "A"s are actually in the other
columns before it. I need the number in the total column to equal "1". For
example, there are 4 classes in one day. If a
student is absent for 2 classes, they are marked absent for the enitre
Click to expand...
day, but the columns would read A, A, P, P. ("A"=absent, "P"=present). I
need to add only the columns that actually have an A in them and then get
the "TOTAL" column to equal "1" so that the workshhet reads that they were
absent for 1 day. If I have 4 teachers who mark them absent for all 4
classes, I need Excel to add up all of the "A"s and
still put the number "1" in the "TOTAL" column so that they are marked
Click to expand...
absent for the day. Now, if the students are marked absent for 1 class and
were present in the other 3, they would not be marked absent for the entire
day, so the "TOTAL" column should read "0". This total column with the "1"s
in it will then be totalled for the week, the month, and the entire course
on a regular basis. I think there may be a
 
Back
Top