Good Morning,
I have people who get paid based on units and gross profit. Sometimes they get full credit and sometimes they get a fraction of the units and gross. I can count the whole units they are involved with by using =SUMPRODUCT(ISTEXT(B4:B22)*($B$4:$B$22=A1)) and I can count the actual units they get paid for by using =SUM(C4,C5,C6,C8,C10,C16,C17,C18,C20,C22) manually. What I would like is to be able to not use the SUM function and still count the number of “U” and “N” including the fractional credits using a SUMPRODUCT (I Think).
The correct answers are in cells C1 & C2
When I attempt to use SUMPRODUCT in the gross columns it counts the entries and not the amounts. The correct amount for the “Paid Gross” is in Cell G4.
Thanks in advance
JJ in SD
I have people who get paid based on units and gross profit. Sometimes they get full credit and sometimes they get a fraction of the units and gross. I can count the whole units they are involved with by using =SUMPRODUCT(ISTEXT(B4:B22)*($B$4:$B$22=A1)) and I can count the actual units they get paid for by using =SUM(C4,C5,C6,C8,C10,C16,C17,C18,C20,C22) manually. What I would like is to be able to not use the SUM function and still count the number of “U” and “N” including the fractional credits using a SUMPRODUCT (I Think).
The correct answers are in cells C1 & C2
When I attempt to use SUMPRODUCT in the gross columns it counts the entries and not the amounts. The correct amount for the “Paid Gross” is in Cell G4.
Thanks in advance
JJ in SD
| NRSG Pay.xls | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | TotalUnits | ActualUnits | TotalGross | PaidGross | Correct | Correct | |||
| 2 | N | 8 | 7 | $8.00 | $8.00 | amountfor | amountfor | ||
| 3 | U | 7 | 51/3 | $7.00 | $7.00 | totalgross | paidgross | ||
| 4 | T | 15 | 121/3 | $15.00 | $15.00 | $825.00 | $625.00 | ||
| 5 | |||||||||
| 6 | N | 1/2 | $50.00 | $25.00 | |||||
| 7 | N | 1 | $20.00 | $20.00 | |||||
| 8 | N | 1 | $60.00 | $60.00 | |||||
| 9 | U | 1/2 | $120.00 | $60.00 | |||||
| 10 | N | 1/2 | $90.00 | $45.00 | |||||
| 11 | U | 1 | $30.00 | $30.00 | |||||
| 12 | N | 1 | $25.00 | $25.00 | |||||
| 13 | U | 1 | $30.00 | $30.00 | |||||
| 14 | U | 1/2 | $100.00 | $50.00 | |||||
| 15 | U | 1 | $75.00 | $75.00 | |||||
| 16 | U | 1 | $60.00 | $60.00 | |||||
| 17 | U | 1/3 | $30.00 | $10.00 | |||||
| 18 | N | 1 | $25.00 | $25.00 | |||||
| 19 | N | 1 | $40.00 | $40.00 | |||||
| 20 | N | 1 | $70.00 | $70.00 | |||||
Sheet3 | |||||||||
