Sumifs with cell range for criteria

[bkrupa]

Hi Everyone,

First post on the forum - I have been a long time reader of this forum and have been able to search out an answer for every question I have ever had until today so I finally had an excuse to make an account.

I am trying to use the below array formula which is a sumifs to return the sum of values in Table! page column P where multiple criteria are met. Where things get a little wonky is in the underlined section where my criteria range is Table!$K$16:$K$2500 and I am wanting to accept a range of criteria matches from cell reference D7:D11.

This is because D7:D11 contain the weeks in the period IE 5,6,7,8 and I am hoping to get a summed period to date number based on matching the other criteria and having any of the up to 5 weeks match as well.

The formula does not error when entered as an array but for some reason the PTD number where all of the weeks are considered is sometimes less than if I just lookup an individual week....

{=IFERROR(SUMIFS(Table!$P$16:$P$25000,Table!$G$16:$G$25000,'By Article'!J17,Table!$J$16:$J$25000,'By Article'!$D$4,Table!$K$16:$K$25000,$D$7:$D$11,Table!$L$16:$L$25000,VLOOKUP('By Article'!G17,'By Article'!$C$12:$D$13,2,FALSE)),"")}


Thank-you so much in advance!

-Brett

 

[Aladin Akyurek]

Rather like this...

=SUMPRODUCT(SUMIFS(Table!$P$16:$P$25000,Table!$G$16:$G$25000,'By Article'!J17,Table!$J$16:$J$25000,'By Article'!$D$4,Table!$K$16:$K$25000,$D$7:$D$11,Table!$L$16:$L$25000,VLOOKUP('By Article'!G17,'By Article'!$C$12:$D$13,2,FALSE)))

 

[bkrupa]

It worked!

I will have to research why/how because I have never used a SUMPRODUCT formula before....it looks like it tells excel that the formula that follows it is an array for a sum equation?

Thanks again a ton!

-Brett

 

[Aladin Akyurek]

It worked!

I will have to research why/how because I have never used a SUMPRODUCT formula before....it looks like it tells excel that the formula that follows it is an array for a sum equation?

Thanks again a ton!

-Brett

It's the same as:

SUMIFS(Table!$P$16:$P$25000,Table!$G$16:$G$25000,'By Article'!J17,Table!$J$16:$J$25000,'By Article'!$D$4,Table!$K$16:$K$25000,$D$7,Table!$L$16:$L$25000,VLOOKUP('By Article'!G17,'By Article'!$C$12:$D$13,2,FALSE))+
SUMIFS(Table!$P$16:$P$25000,Table!$G$16:$G$25000,'By Article'!J17,Table!$J$16:$J$25000,'By Article'!$D$4,Table!$K$16:$K$25000,$D$8,Table!$L$16:$L$25000,VLOOKUP('By Article'!G17,'By Article'!$C$12:$D$13,2,FALSE))+
SUMIFS(Table!$P$16:$P$25000,Table!$G$16:$G$25000,'By Article'!J17,Table!$J$16:$J$25000,'By Article'!$D$4,Table!$K$16:$K$25000,$D$9,Table!$L$16:$L$25000,VLOOKUP('By Article'!G17,'By Article'!$C$12:$D$13,2,FALSE))+
SUMIFS(Table!$P$16:$P$25000,Table!$G$16:$G$25000,'By Article'!J17,Table!$J$16:$J$25000,'By Article'!$D$4,Table!$K$16:$K$25000,$D$10,Table!$L$16:$L$25000,VLOOKUP('By Article'!G17,'By Article'!$C$12:$D$13,2,FALSE))+
SUMIFS(Table!$P$16:$P$25000,Table!$G$16:$G$25000,'By Article'!J17,Table!$J$16:$J$25000,'By Article'!$D$4,Table!$K$16:$K$25000,$D$11,Table!$L$16:$L$25000,VLOOKUP('By Article'!G17,'By Article'!$C$12:$D$13,2,FALSE))

SUMIFS thus returns a value for each cell of $D$7:$D$11, which would like like this...

{15;20;12;0;10}

The outer SUMPRODUCT sums this result array.​

 

[bkrupa]

Thank-you,

I really appreciate the explanation and help on this. Was banging my head against the wall for a while!

-Brett

It's the same as:

SUMIFS(Table!$P$16:$P$25000,Table!$G$16:$G$25000,'By Article'!J17,Table!$J$16:$J$25000,'By Article'!$D$4,Table!$K$16:$K$25000,$D$7,Table!$L$16:$L$25000,VLOOKUP('By Article'!G17,'By Article'!$C$12:$D$13,2,FALSE))+
SUMIFS(Table!$P$16:$P$25000,Table!$G$16:$G$25000,'By Article'!J17,Table!$J$16:$J$25000,'By Article'!$D$4,Table!$K$16:$K$25000,$D$8,Table!$L$16:$L$25000,VLOOKUP('By Article'!G17,'By Article'!$C$12:$D$13,2,FALSE))+
SUMIFS(Table!$P$16:$P$25000,Table!$G$16:$G$25000,'By Article'!J17,Table!$J$16:$J$25000,'By Article'!$D$4,Table!$K$16:$K$25000,$D$9,Table!$L$16:$L$25000,VLOOKUP('By Article'!G17,'By Article'!$C$12:$D$13,2,FALSE))+
SUMIFS(Table!$P$16:$P$25000,Table!$G$16:$G$25000,'By Article'!J17,Table!$J$16:$J$25000,'By Article'!$D$4,Table!$K$16:$K$25000,$D$10,Table!$L$16:$L$25000,VLOOKUP('By Article'!G17,'By Article'!$C$12:$D$13,2,FALSE))+
SUMIFS(Table!$P$16:$P$25000,Table!$G$16:$G$25000,'By Article'!J17,Table!$J$16:$J$25000,'By Article'!$D$4,Table!$K$16:$K$25000,$D$11,Table!$L$16:$L$25000,VLOOKUP('By Article'!G17,'By Article'!$C$12:$D$13,2,FALSE))

SUMIFS thus returns a value for each cell of $D$7:$D$11, which would like like this...

{15;20;12;0;10}

The outer SUMPRODUCT sums this result array.​

 

[Aladin Akyurek]

Thank-you,

I really appreciate the explanation and help on this. Was banging my head against the wall for a while!

-Brett

You are welcome.