Excel - Sumifs with Match

Question

Good day,

I have a table in one sheet (!Vide) which contains Zip Code ($F), Distance ($I), status ($H) and ID Number ($K) and all the zip codes ($A) for a region in another sheet (!NYI)

I am trying to do a SUMIFS if the order is part of the the zip code in the NYI sheet using:

=IFERROR(SUMIFS(Vide!$I:$I,Vide!$M:$M,Tableau!F1,Vide!$H:$H,"L",Vide!$K:$K,">0",IF(MATCH(Vide!$F:$F,!NYI$A:$A,0),"TRUE","FALSE"),"TRUE")

all the part are working fine appart from the IF(MATCH()) part

here is a sample of the data:

Sheet "Vide":

$F         $H         $I         $K
11412      L          105        1390
90210      L          148.6      95.19
11422      E          135.9      2325.6

in this case only row 1 would be taken in account since it meets all the conditions and is present in the table in sheet NYI:

Sheet NYI:

$A          $B
11412       Saint Albans
11413       Springfield Gardens
11422       Rosedale

Any and all help will be appreciated.

Thank you

Answer 1

To make it simpler you have got to use a helper column first:

=IFERROR(MATCH(F1,NYI!$A:$A,0),0)

So that it will have this:

$F         $H        $I       $K            $L
11412      L       105         1390         1
90210      L       148.6       95.19        0
11422      E       135.9       2325.6       3

Then change your formula to:

=IFERROR(SUMIFS(Vide!$I:$I,Vide!$M:$M,Tableau!F1,Vide!$H:$H,"L",Vide!$K:$K,">0",Vide!$L:$L,">1"),"TRUE")

If you need just the formula without a helper column here it is:

=SUMPRODUCT(Vide!$I:$I*--(Vide!$M:$M=Tableau!F1)*--(Vide!$H:$H="L")*--(Vide!$K:$K>0)*--(Vide!$F:$F=NYI$A:$A))

You might wanna limit this last part to only rows with data as it will take so much overhead in formula processing, e.g. (Vide!$F$1:$F$100=NYI$A$1:$A$100)

See Sample below: