Sum Multiple Columns Based On Another Column

[jhudson444]

Hi. I am unable to create a formula which will add two columns based on criteria in another column.

Ex:

A.....B.....C
.......1.....1
.......1.....1
NN...1.....1
.......1.....1

The formula =SUM(B:C) will result in 8 because it will add everything in columns B and C. The formula =SUMIF(A:A,"NN",B:B) will result in 1 because it will add column B whenever there is "NN" in column A.

How do I add both columns B and C whenever "NN" appears in column A?

[Fotis1991]

Do you mean?

=SUMIF(A:A,"NN",B:B)+SUMIF(A:A,"NN",c:c)

[jhudson444]

Yes, I found that the formula =SUMIF(A:A,"NN",B:B)+SUMIF(A:A,"NN",C:C) will add them both. Is there a way to simplify the formula?

[Fotis1991]

Perhaps

=SUMPRODUCT((A1:A1000="nn")*(B1:C1000))

[jhudson444]

That works, except that, whenever there is text in the columns to be added, the formula results in #VALUE!.

[Fotis1991]

Then try in this way.

Please Login or Register  to view this content.

[jhudson444]

That's okay. I've decided to stick with the SUMIF-based formula.

Thanks for your help!

[Fotis1991]

You are welcome and thanks for the feed back.

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Tony Valko]

You can try this array formula**:

=SUM(IF(A2:A10="NN",B2:C10))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

You should avoid using entire columns as range references in array formulas. Use smaller specific ranges.

[jhudson444]

Thanks for the suggestion!

[Tony Valko]

You're welcome!