Hi Ford
"Im not sure how you can get the cell range: L8:L37 should tie back to that of Cell F10 when you are excluding row 11 (BU3)? unless that is covered by "Others"? In which case, all you need to to sum by Code (column K, row 3)and Journal col B <1?"
I think you are looking at Sheet 1 - ignore that, its only used to demostrate the expected results. The only 2 sheets in use are "Journal Data" and "Validation Check"
I have made the change to B7, thanks!
Does no one use: F8=LOOKUP(9.99999999999999E+307,'Journal Data'!DB:DB) to display the last row total? I have used it for a while but it seems people are against it?
I have made the other changes you suggest.
Now onto this one... ![Smilie](https://www.excelforum.com/images/smilies/smile.gif)
On Journal sheet...
L8=SUMPRODUCT(('Journal Data'!$L$3:$DA$3='Validation Check'!$K8)*('Journal Data'!$L$6:$DA$6="Cost")*('Journal Data'!$G$7:$G$107='Validation Check'!J8)*('Journal Data'!$L$7:$DA$107))
This is really close... It will never match with the word "other" as this doesnt appear on the "Journal Data" sheet. What I need it to do is basically if there isnt a code in column C of the sheet "Journal Data" and if it isnt either "Leisure (NC)" or "ES (NC)" then class it as other. Then sum it matching row 3 on the journal data sheet with column K of the validation sheet
I hope that makes sense?
Bookmarks