SUMIFS issues

Indeed problem with ranges as wit one unneeded condition. So SUMIF would also do :-) but with SUMIFS in B3:

Formula:

=SUMIFS(data!B$2:B$1057,data!$L$2:$L$1057,"d")

and copy to right.
similar (but with "n") a row below

PS. You could use also whole columns reference

Formula:

=SUMIFS(data!B:B,data!$L:$L,"d")

Note absence of dolar sign before B and presence before L (this way B will change when copied right and L will not).

This works really well with the example that I attached, thanks very much

If I transpose the data headers, the formula still works but I have to manually change the cell reference, is there a way of encompassing this into the formula to future proof it?

thanks again

I'd use probably sumproduct in this case.
For instance B2 in Formula sheet:

Formula:

=SUMPRODUCT(data!$B$2:$K$1057,(data!$L$2:$L$1057=B$1)*(data!$B$1:$K$1=$A2))

and copy down and right

Sure, use:

Formula:

=SUMPRODUCT(data!$B$2:$K$1057,(data!$L$2:$L$1057=B$2)*(data!$B$1:$K$1=$A3)*(data!$M$2:$M$1057=B$1))

can be copied directly to column D (and if necessary F, H, etc.), but in column C (and copied to E, etc.) slight change (as your row 1 contains merged cels) is required:

Formula:

=SUMPRODUCT(data!$B$2:$K$1057,(data!$L$2:$L$1057=C$2)*(data!$B$1:$K$1=$A3)*(data!$M$2:$M$1057=B$1))

I've had a go at transposing this into a bigger version of the same thing, but it's bringing back the VALUE answer

in my latest effort, instead of just saying D or N, it has Green Amber and red (setup in the same fashion), like in this attached example - could you explain where i'm going wrong please?

***PLEASE IGNORE THE ABOVE, I'M AN IDIOT AND COPIED IT ACROSS INCORRECTLY***

Please try at B3

=SUMIFS(INDEX(data!$B$2:$K$1057,,MATCH($A3,data!$B$1:$K$1,)),data!$L$2:$L$1057,B$2,data!$M$2:$M$1057,LOOKUP("z",$B$1:B$1))