count value based on different cell for multiple criteria with formula

[trimiii]

HI,

I need help... on column A2 I have the result of 6 drivers that are registered based on 6-course category (A1, A2, B1, B2, C1, C2). the daily attendance are listed from F4:k9)
On the column D4:d9, I have result of the daily attendance, weekly total for Absent only
On the column e4:e9, I have the result of the daily attendance, the weekly total for Duty only

Now, what I need is:
1. on the column B2, to count me how many days the student is absent based on the category A1, B1, C1 ( on the columns b4:b9), only for absent (result from column D4:D9), the correct result should be 4
2. on the C2, to count me how many days the student was on duty based on the result on columns E4:E9, the correct result should be 3


see the pic1 for more clarification.

thuoughts...

 

[mamady]

Hi Dear,

Just a clarification, for categories A1, B1 and C1 I can see (4,2,1). Why do you consider the correct result is 4 only?

With regards to the second part, you can simply use the following formula in cell C2 unless there is another requirement that renders this logic inappropriate
=SUM(E:E)

Regards

 

[trimiii]

Hi,

Well, correction in my part... 4,2,1 are the number of the days that students were absent, but the correct result is 3 not 4; that's mean three students were absent during this week.

the criteria for this count is course title (A1, B1, and C1) as you see in the picture, 3 of 6 students were absent during this week (1 or more absent, doesn't matter) the count is for the student, not for the days.

Any student that is enrolled in a different course (such as B2, C2 or C2) should not be counted against this result.

 

[mamady]

In this case, please use the following formula in cell B2
=COUNTIF(D:D,">"&0)