Hello-

Thank you in advance for your help with this!
The spreadsheet I have, helps with keeping track of an employee's salary and the funding source(s) used to cover the employee's paycheck.


To avoid issues, an employee needs to have their paycheck supported/covered at 100% from one or multiple funding sources. An issue will happen if an employee only has 50% of their paycheck covered by one or multiple funding sources.
Funding sources are often limited to a specific time range they can be used to support an employee and this is where things get tricky. To help summarize if a paycheck is going to be covered 100% by one or multiple funding sources, I have tried to use the SUMIFS formula but I can't get it to work for me. The formula I need help with is one that will sum up the total amount each paycheck is being supported at, on pay day.

Attached is this spreadsheet, column 'R' is where I have tried to create a formula to show the amount each paycheck is being supported at. Can anyone help me create a formula that will total column 'G' on a given payday column 'Q', from the various funding sources that can be used within a time range column 'H and I'.

Thank you!

Attached Files

Test SUMIFS.xlsx (16.6 KB, 11 views)

Maybe you should explain why you reference John Doe (D1) in the sumifs when I can't see any correlation!

Also, how about giving some examples of what you would expect in R9, R10 etc?

Q1 Answer: I want the SUMIFS formula to always calculate, so I referenced the Criteria range1 to the Criteria1.

Q2 Answer:What I was hoping to see is the percent being supported. For example 100%, or 50%.

Not sure I still understand why you would reference D1. The sumifs will always calculate, D1 is not neccessary, and furthermore, makes no sense in the formula.

How about some logic? For 7/1/2016, what do you expect, 50% or 100% and why? I see for 7/1/2016 it shows 15% on row 18.

What do you get from knowing that 15%?