Aug 27 2020 07:20 AM
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.
Aug 27 2020 07:43 AM
How do we connect the names Provider A etc. in column A to the names Hurley etc. in column G?
Aug 27 2020 07:50 AM
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.
Aug 27 2020 07:50 AM
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.
Aug 27 2020 07:54 AM
@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
Aug 27 2020 08:01 AM
Aug 27 2020 08:15 AM
@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).
Aug 27 2020 08:25 AM
The attached version demonstrates a solution using SUMIFS formulas, and also a pivot table.
Aug 27 2020 08:25 AM
Sorry, I think I misinterpreted what you were requesting. Is this what you were looking for?
Aug 27 2020 08:31 AM
@PReagan that is exactly what I am looking for! Do I need to make my data a table? I appreciate you so much!
Aug 27 2020 08:32 AM
@Hans Vogelaar this is wonderful! Thank you very much for all of your help!
Aug 27 2020 08:32 AM - edited Aug 27 2020 08:34 AM
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.
Aug 27 2020 10:08 AM
@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?
Aug 27 2020 11:20 AM - edited Aug 27 2020 11:41 AM
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.
Aug 27 2020 11:25 AM
@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 :)