I came up with C4:

=SUMPRODUCT((RRVs!$G$2:$G$6000='Machine Plotter'!$A4)*
            (RRVs!$A$2:$A$6000='Machine Plotter'!C$3)*
            (RRVs!$N$2:$N$6000='Machine Plotter'!$B4)*
            (((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))
This sums D, where G is Selection, A is Week Number, N is Fund, Status is (CONFIRMED or ORDERED or PENDING).

Then just copy down and over as needed.

When dealing with lengthy formulas, especially SUMIFS, SUMPRODUCT, and the like, it's nice to use Alt+Enter to add arbitrary line breaks to keep things organized.