Excel SUMIFS搭配MATCH判断邮编归属的公式问题求助

Hey there! Let's fix that SUMIFS formula for you. The issue with your current formula is how you're checking if the zip code exists in the NYI sheet—your IF(MATCH(...)) approach doesn't align with SUMIFS's syntax, and MATCH throws #N/A errors when it can't find a match, which breaks the entire condition.

Here are a few working solutions tailored to your needs:

Solution 1: SUMIFS with COUNTIF (Simplest for Most Excel Versions) #

This uses COUNTIF to verify if each zip in Vide!$F:$F exists in NYI!$A:$A (COUNTIF returns a value >0 if a match exists, which SUMIFS automatically treats as "true"):

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

• Pro tip: Replace full column references (like $F:$F) with your actual data range (e.g., $F$1:$F$1000) to speed up formula calculation.

Solution 2: SUMPRODUCT (Flexible, Works in All Excel Versions) #

SUMPRODUCT handles array logic natively, letting you stack all your conditions cleanly without special input requirements:

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

Each condition returns 1 if true, 0 if false—only rows where all conditions are true will contribute to the final sum.

Solution 3: Nested IF with SUM (For Older Excel Versions) #

If you're using an older Excel version (pre-365/2021), use this array formula (press Ctrl+Shift+Enter after typing it, not just Enter):

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

Quick Checks to Avoid Common Issues #

• Ensure zip codes in Vide!$F:$F and NYI!$A:$A use the same format (no extra spaces, both text or both numbers—format mismatches will cause matches to fail).
• If you use IFERROR, return 0 (not "0") if you want the result to be a numeric value (easier for future calculations).

内容的提问来源于stack exchange，提问作者Jomathr