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

[JP]

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!

 

[liddlem]

Hi ~x
see the article http://www.excelforum.com/t177884-s titled 'Countin
text cells' on this forum

 

[JP]

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!

 

[Bob Phillips]

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)

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!

 

[liddlem]

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?

 

[JP]

Thank you so much man. It worked great!

 

[Bob Phillips]

--

HTH

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

 

[Bob Phillips]

Thanks for the feedback, and glad to be of help.

Bob