sumproduct? countif??? Help???

  • Thread starter Thread starter jp
  • Start date Start date
J

jp

Hi.

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 (all of the L's from each room are added to
the "Teacher's List" sheet) 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
 
jp said:
Hi.

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 (all of the L's from each room are added to
the "Teacher's List" sheet) 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
Click to expand...

You cannot use a formula that begins
=IF('Room 4'!D7:IB7=9,
The condition for the IF function must be a single TRUE/FALSE value. D7=9 is
such a single value, but not D7:IB7=9. Your formula for September might
appear to be working, but that's just luck; with other data it would fail.

Instead, try something like this:
=SUMPRODUCT(('Room 4'!D7:IB7=9)*('Room 4'!D10:IB43="L"))
I think you will find this works for September, and also for October (when
you replace the 9 by 10).

If you still have problems, I suggest splitting the problem into two so that
you can see intermediate results. Use a simple formula to count the entries
in each column. So, for example, in D44 you might have:
=COUNTIF('Room 4'!D10:D43,"L")
and you copy this across as far as IB44.
Then just add these for the appropriate month, using either
=SUMPRODUCT(('Room 4'!D7:IB7=9)*('Room 4'!D44:IB44))
or
=SUMIF('Room 4'!D7:IB7,9,'Room 4'!D44:IB44)
 
Back
Top