Formula Help

[UC_Chick]

I am trying to figure out how to sum a particular type of vacation leave for particular providers.  There are 9 different types of leave, minimum of 5 providers, and hours for each day of leave.  I need to have an on-going sum of each type of leave for each provider. 

[#2]

@UC_Chick 

How do we connect the names Provider A etc. in column A to the names Hurley etc. in column G?

[#3]

@Hans Vogelaar 

the names in column G should be the Provider A,B, C, D, E.  I forgot to change that.  This is where I want to total each type of leave for each provider. 

[#4]

Hello @UC_Chick,

Perhaps the SUMIFS() function is what you're looking for. As @Hans Vogelaar noted, the data seems incomplete so it would be difficult to provide an exact formula for you to use.

[#5]

@PReagan Yes that is what I am looking for.  I can't figure it out.  Attached is a corrected version-I forgot to change the names

[#6]

@Hans Vogelaar  see edited excel

[#7]

@UC_Chick 

Please refer to the attached workbook.

[#8]

@PReagan it won't work for me.  I am trying to sum each type of leave for each provider.  The sums should be filled in under the respective type (H-O) and provider row (G).   

[#9]

@UC_Chick 

The attached version demonstrates a solution using SUMIFS formulas, and also a pivot table.

[#10]

@UC_Chick 

Sorry, I think I misinterpreted what you were requesting. Is this what you were looking for?

[#11]

@PReagan that is exactly what I am looking for!  Do I need to make my data a table?  I appreciate you so much!

[#12]

@Hans Vogelaar this is wonderful!  Thank you very much for all of your help!

[#13]

@UC_Chick 

You're welcome. Happy to help!

While it is not necessary to format your data as a table, I always find it to be good practice. In your case, your formula would need to be modified slightly should the data not been formatted as a table.

[#14]

@PReagan I am still having issues.  The formulas are giving me 0 sums when they should not be 0.  I have attached a shortened version of what I am actually working on.  To note, I would like to be able to add on and have this be a working copy, so I made the table to include additional rows that are currently blank.  Any suggestions? 

[#15]

@UC_Chick 

The names should be exactly as they appear in the "Provider" column. For example, "Yontz" should instead be "Yontz, Erin". In order to make this formula dynamic, use the following formula in cell H2 and copy as necessary:

=SUMIFS(Table2[[Hours]:[Hours]],Table2[[Provider]:[Provider]],$G2,Table2[[Type of Leave]:[Type of Leave]],H$1)

EDIT:

Additionally, the "Type of Leave" must match EXACTLY the column headers in the table with sums. For example, "Jury" in the sums table should be changed to "Jury Duty" or vice versa.

[#16]

@PReagan thank you! I'm usually not this Excel illiterate!  Good eye for catching that, I knew it had to be a simple mistake I was making.  Thanks again, sending good vibes to you :)

[#17]

@UC_Chick 

My pleasure! Take care!