If THIS and THAT, then add TOTALS

[Anmari]

Hello everyone!

I'm trying to create a formula in order to be able to have the total hours per position an employee has worked on a different sheet on our database so that i just have to go to that sheet and look for an employee name and have their total shifts worked, total hours worked as a "sitter" and total hours worked as a "psw". The information I need is entered in our database as follows:

COLUMN F = SERVICE PROVIDED (services provided are entered under specific codes, ex: Sitter, PSW, etc...)
COLUMN G = EMPLOYEE NAME
COLUMN M = TOTAL HOURS WORKED

On another sheet I'm trying to do: If (specific service and specific employee on sheet1), then add up total hours for all of that person. Example:

EMPLOYEE NAME

SERVICES PROVIDED​

TOTAL HOURS​

JOHN SMITH#001

SITTER​

7.5​

JANE DOE#002

PSW​

11.25​

JANE DOE#002

PSW​

7.5​

JOHN SMITH#001

SITTER​

11.25​

JOHN SMITH#001

PSW​

4.00​

I'm trying to get the formula to do "If JOHN SMITH#001 AND SITTER then add all of total hours (so correct total would be 18.75).

I tried the following formulas, but they do not work:

=IF(AND(DATA!G:G="*#1386*",AND(DATA!F:F="SITTERG",DATA!F:F="SITTER")),"VALUE(1*DATA!M:M",SUM(1*DATA!M:M))

=IF(AND(DATA!G:G="1386",DATA!F:F="SITTER"),"",VALUE(1*(SUM(DATA!M:M))))


Any help would be greatly appreciated!! Would save us so much time at the end of the year lol

Thank you all in advance!

 

[Aladin Akyurek]

Try...

=SUMIFS(TotalHoursRange,ServicesProvidedRange,"SITTER",EmployeeNameRange,"JOHN SMITH#001")

 

[Anmari]

This worked! Thank you!!!

 

[Anmari]

This worked! Would it be possible to do multiple services criteria all in one formula? example:
=SUMIFS(TotalHoursRange,ServicesProvidedRange,"SITTER",EmployeeNameRange,"JOHN SMITH#001")

AND =SUMIFS(TotalHoursRange,ServicesProvidedRange,"SITTERG",EmployeeNameRange,"JOHN SMITH#001")

AND =SUMIFS(TotalHoursRange,ServicesProvidedRange,"PSW",EmployeeNameRange,"JOHN SMITH#001")

AND =SUMIFS(TotalHoursRange,ServicesProvidedRange,"PSWG",EmployeeNameRange,"JOHN SMITH#001")

 

[Aladin Akyurek]

Try...

=SUM(SUMIFS(TotalHoursRange,ServicesProvidedRange,{"SITTER*","PSW*"},EmployeeNameRange,"JOHN SMITH#001"))