subtract and add cells of a column meeting criteria

[lethargos]

Hi,

My goal is to add all the cells of a columns and the subtract the sum of cells of several other columns, meeting a certain criterion.

So I found SUM(IF(...)). SUMIF didn't work because you could add the cells of more than one column (e.g. A2:C:10 doesn't work). So I wanted to add the cells of a column and then subtract the sum of cells of several other columns.

So what I did was something like

{SUM(IF('Sheet 1'!A2:A200="YES",'Sheet 1'!B2:B:200)-SUM(IF('Sheet 1'!A2:A200="YES",'Sheet 1'!C2:F:200))}

My question is, is there a possibility of doing another nested function (I guess an array formula) without having to repeat the "YES" criterion?

The reason why I'm asking this is that this somewhat synthetic formula works only for consecutive columns, but if I have all sorts of non-consecutive columns spread all over the worksheet, then I'd have to write another function (SUM(IF...)) for each group of columns, which not that nice.

Any ideas?

 

[Eric W]

Welcome to the forum.

First, that's not a valid formula. The B2:B:200 construction is not valid. If it works for you, I'd be very surprised. Second, a SUMIFS or a SUMPRODUCT function might work for you. If you could explain exactly what you want added, and with what conditions, and what you want subtracted, with what conditions, we might be able to figure something out. Pictures are very helpful, so consider the HTML Maker in my signature.

 

[lethargos]

Yes, it was B2:B200, of course, I only wrote it wrong here. It obviously wouldn't have worked otherwise.


Why do you suggest SUMIFS? There's only one single criterion. Does SUMIFS support adding several columns? (SUMIF only works with single columns, you cannot write A2:F20, for instance, i.e. you add the cells of a table; I suppose SUMIFS works the same, does it not?)
I simply want to add several columns according to some single string criterion.

Let's say we have 4 columns. The first column has cells containg "yes" and "no" (this is the criteria column). So I'd like to add the 2nd column and the 3rd column and subtract the 4th. Of course, columns 2 to 4 contain numbers.

The formula that I wrote in the first post works, but if I had to add or subtract some other columns besides 2 to 4 (non-consecutive or from another sheet), then I'd have to write the formula again for each column.

So I was asking if there's a way (a nested formula?) to do all these sums/subtractions (or even other mathematical operations) without having to repeat the same criterion - so basically one single formula.

P.S. I don't understand how to install htmlmaker. I downloaded the .zip, execute the .xla, enable macros, then I get "Run-time error '5' Invalid procedure call or argument". I'm using Excel 2016 on MacOS.

 

[Eric W]

I just mentioned SUMIFS as a possibility until I had more information about your requirements. Here's one possibility that might work for you:

	A	B	C	D	E	F
Criteria	Col 2	Col 3	Col 4
yes
no
yes
yes
no
no
yes
yes
yes

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]51[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=SUMPRODUCT(--(A2:A10="yes"),B2:B10+C2:C10-D2:D10)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



The first term (--(a2:A10)="yes") returns a TRUE/FALSE, which the -- turns into 1/0, then that's multiplied by the second term which you can do what you like with, then summed up for the entire range.

It sounds like you followed the steps to install it, but since I'm not on a Mac, I'm limited in how I can help with that. But let me know if the formula here works for you.

 

[lethargos]

Yes, indeed, it works. I learnt something new.

But the problem with this formula is that it adds something while also taking away. Indeed, I don't need to write a new formula to repeat that same single criterion, but I do have to write each column, even if they're consecutive. Within an IF statement, I can actually select a table, such as A2:K170. And in my real excel worksheet, I have quite a few consecutive columns and that makes SUM(IF(...)) more efficient.

 

[Eric W]

You do have a point. There's no real easy way to combine disjoint regions in a formula. I don't really think that's Excel's fault, just that it's complicated to define them. As you've seen, there are several ways to do it, but they all have pluses/minuses. Here's another way to use SUMPRODUCT:

=SUMPRODUCT(--(A2:A10="yes"),SUBTOTAL(9,OFFSET(B2:C2,ROW(A2:A10)-ROW(A2),0))-D2:D10)

The part in red sums up the B:C columns, but unless you have many columns, it's longer than the original way.

If you're looking for a shorter formula, you could write a UDF, and the calling function would be something like:

=MySum(A2:D10)

and the logic to determine what gets added would be in the VBA code.

So there are several ways to do it, you just have to decide which way works best for you.