Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to sum when the criteria and the range are in the same row?
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to sum when the criteria and the range are in the same row?
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 -- __________________________________ HTH Bob "Mo" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to sum when the criteria and the range are in the same row
Bob... you just saved my life, man. Wow... Thank you very much!
M "Bob Phillips" wrote: 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 -- __________________________________ HTH Bob "Mo" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumIf - when I fill down the Range, Criteria & sum range changes | Excel Worksheet Functions | |||
RANGE EXCEL copy cell that meets criteria in a range | Excel Worksheet Functions | |||
match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
Counting from one range to another range, multiple criteria | Excel Discussion (Misc queries) | |||
Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) |