SUM Based on Two Criteria

[sadithomas]

Hello all,

I'm currently making a spreadsheet for my terminal staff that tracks attendance. I want a spreadsheet set up to give a total number of occurrences based on the type of absence category. Some absences cannot count against your allotted total here, and some only count as half occurrences. I'm having a difficult time trying to set the criteria for these two categories using the SUMIF function (if this is indeed the function I should use). So here's what I have:

Column A Category: All absences that fall under this category count as 1 total occurrence

Column B Category: All absences that fall under this category do not count as an occurrence (but still need to be documented)

Column C Category: All absences that fall under this category count as 0.5 total occurrence

And then I have a column to total these occurrences based on the above criteria, where the formula will yield its results.

So, for example, an employee has 3 Category A absences, 2 Category B absences, and 3 Category C absences. The total the formula should yield is 4.5.

Is anyone able to point me in the right direction?

Thanks!
- Sadi

 

[Mackers]

Hi Sadi

Lots of ways to do this one. A very simple way would be to stick two COUNTIFS() together, so:

=COUNTIFS(EmployeeList, RequiredEmployee, CategoryAList, "Yes") + 0.5*COUNTIFS(EmployeeList, RequiredEmployee, CategoryBList, "Yes")

You will need to change "Yes" to however it is indicated, so if there is a 1 you should put ..CategoryAList, 1), or if it is a date then you could do ">0" or something.

Hope that helps

Mackers

 

[Ron Coderre]

With this sample data in A1:D13

Employee  Full Excused    Half
Alpha        1                
Alpha        2                
Alpha                1        
Alpha                1        
Alpha                        1
Alpha                        2
Bravo        2                
Bravo        2                
Bravo        2                
Bravo                2        
Bravo                        3
Bravo                        4

and
E1: an employee to analyze....Alpha

This formula returns the calculated absences

=SUMPRODUCT(SUM(($A$2:$A$20=E1)*$B$2:$B$20,($A$2:$A$20=E1)*D2:D20*0.5))

In that example, the formula returns: 4.5

If E1 is Bravo, the formula returns: 9.5

Is that something you can work with?