CODEWITHSUNDEEP
excel
JPVoogt
JPVoogt

Reputation: 538

SUM all fields in an column, except for n specified range

I want to sum the values of column B where column A does not contain specified fields or blank fields. I have tried the following, but does not work

=SUMPRODUCT(ISNA(MATCH(B:B,{"ASY999","ASY002","CIB001"},0))*(G:G<>""))-1

I need to reference an entire column, for future expansion on the data. Here is my sample data that i am trying to conditionally sum in EXCEL 2007.

**A      B**
        5.00
        2.00
        2.00
        2.00
ASY999  1.00
AFS001  4.00
ASY002  5.00
AFS001  5.00
CIB001  2.00

Upvotes: 0

Views: 2686

Answers (2)

barry houdini
barry houdini

Reputation: 46341

Try this with SUMPRODUCT as shown below

=SUMPRODUCT(ISNA(MATCH(A:A,{"ASY999","ASY002","CIB001"},0))*(A:A<>""),B:B)

although SUMIFS should be better for a small number of excluded values, it's faster.

=SUMIFS(B:B,A:A,"<>ASY999",A:A,"<>ASY002",A:A,"<>CIB001",A:A,"<>")

Upvotes: 1

lori_m
lori_m

Reputation: 5567

In addition to the formulas already given for backward compatibility you could use:

=SUM(B:B,-SUMIF(A:A,{"ASY999","ASY002","CIB001",""},B:B))

If you're considering future expansion of data, I'd suggest using the Table feature on the insert Tab in Excel 2007 instead of referring to full columns.

Upvotes: 2

Related Questions