count value based on different cell for multiple criteria with formula

trimiii

Board Regular
Joined
May 15, 2018
Messages
67
Office Version
  1. 365
Platform
  1. Windows
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...
 

Attachments

  • pic1.PNG
    pic1.PNG
    23 KB · Views: 9

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

mamady

Board Regular
Joined
Sep 23, 2011
Messages
161
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 15, 2018
Messages
67
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 23, 2011
Messages
161
Office Version
  1. 365
Platform
  1. Windows
In this case, please use the following formula in cell B2
=COUNTIF(D:D,">"&0)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,116
Messages
5,546,031
Members
410,721
Latest member
adi772
Top