conditionally sum values based on the TOTAL of multiple columns being greater than 0

[WisdomSeeker]

I need to write an Excel formula that will sum the values in Column A, but only the values on rows where the sum of Column B + Column C + Column D is greater than zero. That is, my criterion involves multiple columns. I'm looking for a way to do this without a macro, and without having to create an additional column in my data which itself sums Column B + Column C + Column D.

I can't find how to get SUMIF, SUMIFS, or SUMPRODUCT to do this.

With SUMIF it seems my criterion can only be one column, or else my sum_range gets messed up.

With SUMIFS I only seem to be able to have one column involved in each criterion, not add columns together to form a criterion. And multiple criteria are processed as AND, not OR. So I can only get it to include rows that have Column B > 0, Column C > 0, AND Column D > 0.

With SUMPRODUCT I'm trying:
=SUMPRODUCT(((B2:B6>0)+(C2:C6>0)+(D2:D6>0))*A2:A6)

But this adds the same row multiple times if it has a positive value in more than one of the columns, B, C, D.

Any way to get this to work? Perhaps an array formula?

Also, once I get this working I want to add an additional criterion that must also be true: Column E must be blank, after having TRIM applied to its value (LEN(TRIM([value]) <= 0).

Thank you.

[Leith Ross]

Hello WsidomSekker,

Did you try this formula yet?


=IF(SUM(B1:D1)>0,SUM(B1:D1),"")

[WisdomSeeker]

I solved this myself through a combination of Excel's "Evaluate Formula" tool, and the post "SUMPRODUCT Step By Step - Part 3" from xld, which apparently I am not allowed to post a link to.

To do literally what I said, I've now got:
=SUMPRODUCT((B2:B6+C2:C6+D2:D6>0)*(LEN(TRIM(E2:E6))<=0)*(A2:A6))

However what I actually meant to say about Columns B, C, D is that I want the row to be included if any of these is greater than zero. This is slightly different. I've got it working like this:
=SUMPRODUCT((((B2:B6>0)+(C2:C6>0)+(D2:D6>0))>0)*(LEN(TRIM(E2:E6))<=0)*(A2:A6))

SUMPRODUCT is quite powerful and versatile!

Thanks xld.

[Bob Phillips]

SUMPRODUCT is very versatile (but it can be a bit of a resource hog if over-used, just like array functions). You could take a look at my seminal paper (http://www.xldynamic.com/source/xld.SUMPRODUCT.html) on the topic.

[WisdomSeeker]

Thanks xld. Yeah I'm using a bunch of SUMPRODUCT formulas in a few worksheets, which are part of a large, complex workbook. If my formulas slow down workbook performance noticeably, perhaps I'll switch to macro code for these calculations that is triggered whenever a user activates one of the worksheets where these calculations show up.