Excluded Criteria range for a sumproduct/sumif formula

[AnAppleADay]

Hi all,

I have attached a clean workbook here as a link to my OneDrive: Jazz.xlsx

Currently my value returned, is based on this formula:

=SUMPRODUCT(SUMIFS(Loading!$K$2:$K$935,Loading!$M$2:$M$935,PackTypes!$A$3:$A$12,Loading!$L$2:$L$935,D$2))

Returning the value in column K of LOADINGS sheet, while checking against the criteria for pack type (Column M) and week of arrival (Column L), and placing the value in the correct column of the main sheet 'JAZZ'

I now want to be able to EXCLUDE the container numbers (LOADINGS/Column C) which i'll add to a range on the sheet EXCLUSIONS, but i can't fathom what clause to use?!

Any ideas?

 

[jasonb75]

I think that this should do it, not done a thorough test though. Must be array confirmed with Ctrl Shift Enter. Formula to go into D10, then drag right.

=SUMPRODUCT(SUMIFS(Loading!$K$2:$K$935,Loading!$M$2:$M$935,PackTypes!$A$3:$A$12,Loading!$L$2:$L$935,F$2))-SUMPRODUCT(SUMIFS(Loading!$K$2:$K$935,Loading!$M$2:$M$935,PackTypes!$A$3:$A$12,Loading!$L$2:$L$935,F$2,Loading!$C$2:$C$935,TRANSPOSE(Exclusions!$A$1:$A$8)))

 

[AnAppleADay]

This worked first time. Thanks very much!

Can I ask why we need to use transpose in the second sumproduct clause?

thanks again ????????

 

[jasonb75]

Say for a simple example that you have PackTypes a, b and c with Exclusions 1, 2 and 3.

Without transposing, the formula would only look for PackType a and Exclusion 1, PackType b and Exclusion 2 or PackType c and Exclusion 3, it would not look for PackType a and Exclusion 2, PackType a and Exclusion 3, PackType b and Exclusion 1, etc.

Note that you can not have more than 2 criteria lists in a single formula, 1 vertical and 1 horizontal (the horizontal list being the transposed one), all other criteria must be single items.
There are ways to have more lists, but not with functions like sumifs.

 

[Peter_SSs]

Another possible option (I think)

1. Remove the errors from column L of 'Loading' by amending its formula to
=IFNA(VLOOKUP($I2,Weeks!$A$3:$B$194,2,0),"")

2. Try this formula structure in Jazz
=SUMPRODUCT(Loading!$K$2:$K$935,--ISNUMBER(MATCH(Loading!$M$2:$M$935,PackTypes!$A$3:$A$12,0)),--(Loading!$L$2:$L$935=F$2),--ISNA(MATCH(Loading!$C$2:$C$935,Exclusions!$A:$A,0)))


BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.