How to sum when the criteria and the range are in the same row?

[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?

 

[Bob Phillips]

Earned: =SUMPRODUCT(--(MOD(COLUMN(E2:CC2),5)=0),D2:CB2)
Paid: =SUMPRODUCT(--(MOD(COLUMN(E2:CC2),5)=0),--(E2:CC2="Y"),D2:CB2)
Balance: Earned-Paid

 

[Mo]

Bob... you just saved my life, man. Wow... Thank you very much!

M