Sumproduct - adding additional elements

What are you trying to accomplish by your LEN() statement? Is it to just pull values that aren't zero? Why not just reference the range 'Journal Data'!C7:$C$107 and pull values that <>0? Same with cell F9.

Along with this, your value for F8 seems excessive. Why do a lookup when you could reference the total column cell directly? Or do a MAX() on column DB to find the max value?

Hello
I have finally figured out a formula
L8 =SUMPRODUCT((OFFSET('Journal Data'!$L$7:$DA$107,0,MATCH(K8,'Journal Data'!$L$3:$DA$3,0)-1,101,MAX((INDEX(MAX(('Journal Data'!$L$3:$DA$3=K8)*(COLUMN($L:$DA))),0,0))-(MATCH(K8,'Journal Data'!$A$3:$DA$3,0)-1))))*(J7='Journal Data'!$G$7:$G$107))

I was unable to incorporate the "others" criteria
But please do check this formula....i think this is one of the most complex formula I have ever made....lol
Hope this helps!!

Just so I am able to understand what is happening here, I get how F8 is calculated (although, this could be simplified with a simple MAX('Journal Data'!DB:DB)), but what is F9 doing? I am assuming it is summing the values in column DB in Journal Data that corresponds to a value (not a blank) within the same row in column C of the same sheet.

If this is correct, why not use <>"" (is not blank) vs. LEN()>0? It seems the results from F9 are incorrect upon inspection.

yes mcmahobt is correct... it seems that you are making problems complex....max in F8 and <>"" instead of len() would be a much better option

You can still use SUMPRODUCT() and "<>" when cells contain "", you just have to use the "--" operator, and multiply by your value range essentially multiplying by zero. This is a speedier process than using LEN() for calculations regarding big data.

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?

A few observations...
on sheet Journal, you can change DB...
=SUMPRODUCT(--(MOD((COLUMN(K7:DA7)-COLUMN(H1)),2)=0),K7:DA7)
to...
=SUMIF($L$6:$DA$6,DB$6,$L7:$DA7)
copied down and across

and seeing as DB will never be "", you can shorten ...
B7=IF(AND(DB7<>"",C7<>""),MAX(B$6:B6)+1,"")
to just...
=IF(C7="","",MAX($B$6:B6)+1)

On Validation sheet...
F8=LOOKUP(9.99999999999999E+307,'Journal Data'!DB:DB)
could be...
=INDEX('Journal Data'!DB:DB,MATCH("Overall result",'Journal Data'!$G:$G,0))
or just =DB108
F9=SUMPRODUCT((LEN('Journal Data'!C7:$C$9651)>0)*('Journal Data'!$DB$7:$DB$9651))
could be...
=SUMIF('Journal Data'!$B$7:$B$106,">0",'Journal Data'!$DB$7:$DB$107)

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))
copied down

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 used your 1st D/L, thats the only 2 sheets there

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 wouldnt say that. If your are comfortable with something, use it - just keep in mind that there may be other, more efficient, ways of doing some things

To try and simplify this, I added a helper column to JD sheet (I used F, use what you want, and it can be hidden), then copied this down...
=IF(OR(G7="LEISURE (NC)",G7="ES (NC)"),G7,"Other")

Then I changed the formula in VD to this, copied down...
=SUMPRODUCT(('Journal Data'!$L$3:$DA$3='Validation Check'!$K8)*('Journal Data'!$L$6:$DA$6="Cost")*('Journal Data'!$F$7:$F$107='Validation Check'!J8)*('Journal Data'!$L$7:$DA$107))

Hello
Use these formulas

F7 =IF(C7="",IF(OR(G7="LEISURE (NC)",G7="ES (NC)"),G7,"Other"),"")
and copy down

L8 ==SUMPRODUCT(('Journal Data'!$L$3:$DA$3=$K8)*('Journal Data'!$L$6:$DA$6="Cost")*('Journal Data'!$F$7:$F$107=J8)*('Journal Data'!$L$7:$DA$107))
and copy down

Hope this helps!!

You can also give a try to this array formula which works without the helper column F

=SUMPRODUCT(('Journal Data'!$L$3:$DA$3=$K8)*('Journal Data'!$L$6:$DA$6="Cost")*IF(J8="Other",('Journal Data'!$G$7:$G$107<>"Leisure (NC)")*('Journal Data'!$G$7:$G$107<>"ES (NC)"),('Journal Data'!$G$7:$G$107=J8))*('Journal Data'!$C$7:$C$107="")*('Journal Data'!$L$7:$DA$107))

Hope this helps!!

Using both these methods the total is matching cell F10

Apologies, change the helper to this...
=IF(ISNUMBER(B7),"",IF(OR(G7="LEISURE (NC)",G7="ES (NC)"),G7,"Other"))