Formula help please!!!!!!!!!!!

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

Guest

Hello. I am doing a spreadhsheet to keep track of attendance in my classroom. I have the students names in column A7-A40. I am looking to see how can I add up how many "A"'s (for absent) a particular student got over the course of a trimester. I have a combo box on a second page at B2 and a box that adds up the number of "A's".

In each column on the first page I have three boxes stacked on top of each other- which trimester the date is in, the date, and then an "A" for absent or "L" late. It looks like this

Column
5 Trimester
6 Dat
7 "A", or "L" for "absent" or "late

Is there a way that if it is the first trimester (so I would put a "1" where trimester is, I can add up how many "A"'s or "L"'s a certain student has over the course of the trimester (if say the trimester starts at column J and ends at column Z?) Is there a formula to add up the number of "a"'s a certain student got?

I can't figure out a formula to use to find this. I have tried to us a vlookup, but it seems to only work for one specific column. I need to add up the total of "A"'s or "L"'s in about 30 different columns if there is an "A" or "L" there.

Thanks so much for any help!
 
Okay I looked at that. That would normally work but I have a cell that I input which trimester a date is in. I want to see if there is a way that if are three dates in a row that are in the first trimester and a students has been absent and marked with a "A", then it will add 1 each time to a box on another page of the worksheet. I mark which trimester the date is in with a 1,2, or 3 in the cell above the date. This cell and the "A" or "L" (for "late") are what I want to link somehow. I want to see if there is a way to add up how many "A"'s or "L"'s a student got over the course of three months or so. The countif function might work but the second sheet has a combo box that has all of the students names' in it. Would need a vlookup for that? But how would I use multiple formulas to solve this problem? I am so lost on a formula it is not even funny. Thanks for any help!
 
JP,

Let's assume that the combobox is linked to cell A1 on the second sheet, so
you can get the value, and that the columns span J-AZ.

=SUMPRODUCT((Sheet1!A7:A40=A1)*(Sheet1!J5:AZ5=1)*(Sheet1!J7:AZ40="A"))

Just change the cells to suit

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

JP said:
Okay I looked at that. That would normally work but I have a cell that I
Click to expand...
input which trimester a date is in. I want to see if there is a way that if
are three dates in a row that are in the first trimester and a students has
been absent and marked with a "A", then it will add 1 each time to a box on
another page of the worksheet. I mark which trimester the date is in with a
1,2, or 3 in the cell above the date. This cell and the "A" or "L" (for
"late") are what I want to link somehow. I want to see if there is a way to
add up how many "A"'s or "L"'s a student got over the course of three months
or so. The countif function might work but the second sheet has a combo box
that has all of the students names' in it. Would need a vlookup for that?
But how would I use multiple formulas to solve this problem? I am so lost on
a formula it is not even funny. Thanks for any help!
 
What about creating another 'field' that combined the Trimester and the
"A" or "L" condition.

For example
Row5 = Trimester
Row6 = A or L
Cell B7 = '=B5 & B6'


Then have a summary table (on the other spreadsheet) that contains 6
cells

Trimester 1 2 3

AbsentCount 5 1 0
LateCount 0 2 23

In the 'COUNT' fields, type in the formula for each condition.
+Countif(Range,'=1A')
+Countif(Range,'=1L')

Is this a workable solution for you?
 
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top