D4 in your attachment appears to match up to: Burton / OPEX / 1.

Did you mean down in D16?

The formula uses intentional relative referencing and is meant to adjust itself as you copy and paste it around.

The equivalent formula for D16 would be:

=SUMPRODUCT((RRVs!$G$2:$G$6000='Machine Plotter'!$A16)*
            (RRVs!$A$2:$A$6000='Machine Plotter'!D$3)*
            (RRVs!$N$2:$N$6000='Machine Plotter'!$B16)*
            (((ISNUMBER(SEARCH("CONFIRMED",RRVs!$AA$2:$AA$6000)))+(ISNUMBER(SEARCH("ORDERED",RRVs!$AA$2:$AA$6000)))+
              (ISNUMBER(SEARCH("PENDING",RRVs!$AA$2:$AA$6000))))>0)*
            (RRVs!$D$2:$D$6000))
Notice how the criteria has adjusted itself. The first range criteria now points to $A16, the 2nd points to D$3, the 3rd is $B16 and so on.

You can use D16 as a launching point, since that formula is "relative" to anywhere else. Copy that cell and paste it into other cells that you want to apply it to; the formula will correct itself. Do not copy the exact formula out of that cell into others, copy the cell.