countif? Please help!!!

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

Guest

Hello

I am doing a Lunch Detention spreadsheet for my
school and I have included 18 different classes on 18
different sheets, with all of the students from the
different classes on their respective class rosters. I
also have at the front of the class sheets a separate
sheet that includes a list of teachers and a box for each
month of the school year to add up how many lunch
detentions a specific class has in a single month. I also
have a box above each day of the school year (D7:IB7) and have the
number of the month that we are in above it to separate
the months (Jan. ="1", Feb="2" and so on). If a student
has lunch detention, I put an "L" in students box (D10:IB43).

I used the "countif" function and it worked for the month of september but none of the other months will work. I used this formula for my "Room 4" sheet on my "Teacher's List" sheet for September
=IF('Room 4'!D7:IB7=9,COUNTIF('Room 4'!D10:IB43,"L"),""

This formula adds up the total # of L's for the entire year, but I only want it for the specific month of September (D10:AC43).

This worked fine. But I tried to October and it wouldn't work. Here is the formula I used
=IF('Room 4'!D7:IB7=10,COUNTIF('Room 4'!D10:IB43,"L"),""

Is there anyway to just have it add automatically all of the cells that are under the same month (if there is a "9" in the month box, all of the L's in September would be added)???

I'm so sorry if this is all over the place. I am frustrated because I have being trying to figure this out for the past 2 days straight

Thanks for any help
 
Hi JP

Try using Sumproduct instead
=SUMPRODUCT(('Room 4'!D7:IB7=9)*('Room 4'!D10:IB43,"L"))

--
Regards
Roger Govier
JP said:
Hello.

I am doing a Lunch Detention spreadsheet for my
school and I have included 18 different classes on 18
different sheets, with all of the students from the
different classes on their respective class rosters. I
also have at the front of the class sheets a separate
sheet that includes a list of teachers and a box for each
month of the school year to add up how many lunch
detentions a specific class has in a single month. I also
have a box above each day of the school year (D7:IB7) and have the
number of the month that we are in above it to separate
the months (Jan. ="1", Feb="2" and so on). If a student
has lunch detention, I put an "L" in students box (D10:IB43).

I used the "countif" function and it worked for the month of september but
Click to expand...
none of the other months will work. I used this formula for my "Room 4"
sheet on my "Teacher's List" sheet for September-
=IF('Room 4'!D7:IB7=9,COUNTIF('Room 4'!D10:IB43,"L"),"")

This formula adds up the total # of L's for the entire year, but I only
Click to expand...
want it for the specific month of September (D10:AC43).
This worked fine. But I tried to October and it wouldn't work. Here is the formula I used:
=IF('Room 4'!D7:IB7=10,COUNTIF('Room 4'!D10:IB43,"L"),"")

Is there anyway to just have it add automatically all of the cells that
Click to expand...
are under the same month (if there is a "9" in the month box, all of the L's
in September would be added)???
I'm so sorry if this is all over the place. I am frustrated because I have
Click to expand...
being trying to figure this out for the past 2 days straight.
 
Back
Top