countif problems...

Originally Posted by moshmoshon

Hello everybody, I have a question. In the attached file you can see I have a table that contains details about cancellations of lessons, their date and the week number (from the beginning of the course).
The two right tables contain statistics about the right table for a selected week (as noted in cell H1).

I want to make the two right tables automatic. Changing the week number in cell H1, will cause the statistics tables to update.
The problem is that I need to put two conditions for each table : In the first one, I need to consider the week number as well as the day in the week. In the second table, I need to consider the week number as well as the cause for the cancellation : (No instructor, Technical, Lateness ,student sickness). How can I do it?
Countif doesn't help me because it can contain only one condition, and Vlookup doesn't help as well.
Any ideas?

Thanks a lot.

Hi,

in H4

=SUMPRODUCT(--(B$2:B$23=$H$1)*(--(C$2:C$23=G4)))

fill down 4 places

in H13

=SUMPRODUCT(--(B$2:B$23=$H$1)*(--(E$2:E$23=G13)))

fill down 4 places.

hth
---