# SUM Based on Two Criteria

##### New Member
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!

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

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

With this sample data in A1:D13
Code:
``````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
Code:
``=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?

Replies
11
Views
814
Replies
2
Views
159
Replies
4
Views
968
Replies
6
Views
360
Replies
6
Views
521

1,203,401
Messages
6,055,182
Members
444,768
Latest member
EMGVT

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back