Need formula that includes TOTAL from 2 different worksheets based on multiple criteria
I am looking for a formula that includes total hours worked from 2 separate invoices located on 2 separate worksheets. I used the formula below:
[code] =INDEX(INV.A.WK.1[[Standard ]],MATCH(1,(INV.A.WK.1[Last Name]=$A17)*(INV.A.WK.1[First Name]=$B17)*(INV.A.WK.1[Location]=$AK17)*(INV.A.WK.1[Division]=$AJ17),0)) [/code]
however, instead of leaving the cell at "0" or leaving it blank, the cell contains "#N/A" which does not allow me to total the column with a value. I have attached the file for better understanding. Please refer to the BLUE tab titled "Bi-Weekly Summary Report".
Also, the above formula only includes values from one worksheet and I need to add a second worksheet. I tried the following code, but this did not work...
[code] =INDEX(AND(INV.A.WK.1[[Standard ]],INV.B.WK.1[[Standard ]]),MATCH(1,AND(INV.A.WK.1[Last Name]=$A17,INV.B.WK.1[Last Name]=$A17)*AND(INV.A.WK.1[First Name]=$B17,INV.B.WK.1[First Name]=$B17)*AND(INV.A.WK.1[Location]=$AK17,INV.A.WK.1[Location]=$AK17)*AND(INV.A.WK.1[Division]=$AJ17,INV.A.WK.1[Division]=$AJ17),0))[/code]
Thanks :(
| Attachment | Size |
|---|---|
| SAMPLE_(091612).xlsx | 160.13 KB |
RE: Need formula...
Hi,
Use your first formula. Make the same for the second worksheet and sum them:
= INDEX(INV.A.WK.1[[Standard ]], MATCH(1, (INV.A.WK.1[Last Name] = $A17) * (INV.A.WK.1[First Name] = $B17) * (INV.A.WK.1[Location] = $AK17) * (INV.A.WK.1[Division] = $AJ17), 0)) + INDEX(INV.B.WK.1[[Standard ]], MATCH(1, (INV.B.WK.1[Last Name] = $A17) * (INV.B.WK.1[First Name] = $B17) * (INV.B.WK.1[Location] = $AK17) * (INV.B.WK.1[Division] = $AJ17), 0))
To avoid #N/A error wrap the above formula in the following one:
= IFERROR(Above_Formula, 0)
Something in addition:
1) You have four worksheets: INV-A Week 1, INV-B Week 1, INV-A Week 2, INV-B Week 2.
2) In these worksheets you have a columns "Standard", "Overtime" for "Rates" and "Standard ", "Overtime " for "Hours". The columns from "Hours" group have one additional space character at the end.
3) What I mean is that the above rule is not valid for the INV-B Week 2 worksheet. There you have "Standard ", "Overtime " for "Rates" and "Standard", "Overtime" for "Hours" and thus you get incorrect sum of corresponding cells for Week 2 in the BI-WEEKLY SUMMARY REPORT worksheet.
4) Of course this may be is your aim but I mention it in case it is not.
Best regards.
SOLVED! and GREAT CATCH!
Thank you so much for your help. You have solved my problem and caught an error that would have baffled me for far too long! I really appreciate your help on this issue!