Attendance Spreadsheet

I created a drop down list of children's names and plaeced the list under
every month of the school year. I need a formula that will discern a child's
name from the drop down list and count the number of present days ("P") and
the number of absent days ("A") for each month. From there I can sum the
months to get the total for the year.

Hope someone can help!

--
-PigFox

Assuming you have this drop-down selector box in A2, names listed in A5:A100,
Ps or As from B5:AF100 (to accommodate 31 days), and you want the count of Ps
in B2 and As in C2, then
in B2 use this formula
=SUMPRODUCT((A5:A100=A2)*(B5:AF100="P"))
then in C2 use
=SUMPRODUCT((A5:A100=A2)*(B5:AF100="A"))

Honestly, it's just as easy to insert a total directly beside the name
instead. So say the first student is in A5 and you start recording your
attendance in column D (instead of B), then in B5 you could use this formula:
=COUNTIF(D5:AH5,"=P")
and in C5 you could use
=COUNTIF(D5:AH5,"=A")
then just copy B5:C5 down to row 100 and you'll see any student's total Ps
and As for the month in an instant (no drop-down selector required, just scan
your alphabetical list of names and there's the 2 totals you want).

And you definitely should not have to manually add each month's total Ps and
As for a student. I would recommend you setup a totals worksheet which
provides the total Ps and As for each student for each month and a grand
total for the year. You could put the students in column A, total Ps for the
year in column B and total As for the year in column C, then January Ps in
column D and January As in column E, etc. The formulas would be
B2=SUM(D2,F2,H2,J2,L2,N2,P2,R2,T2,V2,X2,Z2)
C2=SUM(E2,G2,I2,K2,M2,O2,Q2,S2,U2,W2,Y2,AA2)
D2=SUMPRODUCT((Jan!A5:A100=A2)*(Jan!B5:AF100="P"))
E2=SUMPRODUCT((Jan!A5:A100=A2)*(Jan!B5:AF100="A"))
F2=SUMPRODUCT((Feb!A5:A100=A2)*(Feb!B5:AF100="P"))
and so on...
These were assuming you want to stick with your drop-down selector idea. If
you wind up making each month's column B have each student's total Ps and
column C have each student's total As, then just modify the sumproduct
formulas above from Month!B5:AF100="P" to instead be Month!B5:B100 and change
Month!B5:AF100="A" to instead be Month!C5:C100
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"PigFox" wrote:

I created a drop down list of children's names and plaeced the list under
every month of the school year. I need a formula that will discern a child's
name from the drop down list and count the number of present days ("P") and
the number of absent days ("A") for each month. From there I can sum the
months to get the total for the year.

Hope someone can help!

--
-PigFox