M
Mo
I'm tracking many data points for each of 10 contract employees.
Additionally, I must collect the same data for each pay period (twice/month).
I use 1 worksheet per quarter; thus, each worksheet captures the information
for ~ 6 pay periods.
Finally, all of the information captured for each contractor is in the same
row. For example:
Name Hours Billed Bill Rate $Earned Resource Paid? Date paid
Rose 12.5 65.00 812.50 Y
12/31
Jim 8.34 42.00 350.28 N
tbd
Etc.
Again, the above information is captured for each bi-weekly pay period in
the quarter. Meaning, the spreadsheet is wide - goes all the way to cell CD.
My issue: For each contractor, I would like a snapshot to understand (1)
the sum of what they've earned to date, (2) what I've paid each to date, and
(3) the balance I owe each. (Saves all the scrolling and looking for "Y" or
"N" in the "Resource Paid?" cells.)
I am able to sum up the "$ Earned to Date". That's simple enough.
What I can't figure out is how to write an equation that looks at the
"Resource Paid?" field and, if it's a "Y", adds up the "$Earned" field, such
that I know how much I've paid each contractor to date.
I tried the SUMIF function where I chose the 6 "Resource Paid?" cells as the
range, "Y" as the criteria, and the 6 cells of "$Earned" as the sum_range.
But it doesn't like it.
Can anyone make a suggestion?
Additionally, I must collect the same data for each pay period (twice/month).
I use 1 worksheet per quarter; thus, each worksheet captures the information
for ~ 6 pay periods.
Finally, all of the information captured for each contractor is in the same
row. For example:
Name Hours Billed Bill Rate $Earned Resource Paid? Date paid
Rose 12.5 65.00 812.50 Y
12/31
Jim 8.34 42.00 350.28 N
tbd
Etc.
Again, the above information is captured for each bi-weekly pay period in
the quarter. Meaning, the spreadsheet is wide - goes all the way to cell CD.
My issue: For each contractor, I would like a snapshot to understand (1)
the sum of what they've earned to date, (2) what I've paid each to date, and
(3) the balance I owe each. (Saves all the scrolling and looking for "Y" or
"N" in the "Resource Paid?" cells.)
I am able to sum up the "$ Earned to Date". That's simple enough.
What I can't figure out is how to write an equation that looks at the
"Resource Paid?" field and, if it's a "Y", adds up the "$Earned" field, such
that I know how much I've paid each contractor to date.
I tried the SUMIF function where I chose the 6 "Resource Paid?" cells as the
range, "Y" as the criteria, and the 6 cells of "$Earned" as the sum_range.
But it doesn't like it.
Can anyone make a suggestion?