SUMIF, with both the sum range and the criteria range including N/A Values

[ad2097]

I have a criteria range B1:UK1 and sum range C2:UK2. Both rows include numbers as well as "N/A" values. Can I set up a sumif/sumifs function that ignores the N/A values? Or is there a different function I could use to solve this issue.

[Pete_UK]

Couldn't you do something to get rid of the #N/A values in the first place? How do they get there? Through some formula? If so, you can use this:

=IFERROR(your_formula,"")

Hope this helps.

Pete

[CK76]

If it is text "N/A" then simple sumif would work. But from your description, criteria range and sum range's dimension do not match (criteria has one extra cell).
If both start as Column B... then something like...
=SUMIFS(B2:UK2,B1:UK1,"<>N/A",B1:UK1,SomeOtherCondition)

If it's #N/A! Error value that's in range... (i.e. error generated from formula evaluation). Best practice is to nest the formula in the range with IFERROR(formula,"") to avoid returning error value.

If you need to retain the error value, you can use SUMPRODUCT (confirmed as array CTRL + SHIFT + ENTER)
=SUMPRODUCT(NOT(ISNA(B1:UK1))*IF(ISNA(B2:UK2),0,B2:UK2)*(B2:UK2="SomeOtherCondition"))

[ad2097]

They had the same range, my original post was inaccurate. Got it solved by nesting previous formulas in the IFERROR construct. Thanks for the help.

[Pete_UK]

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete