# subtract and add cells of a column meeting criteria

#### lethargos

##### New Member
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
Code:
``{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?

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.

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.

ABCDEF
1CriteriaCol 2Col 3Col 4
2yes111251
3no2123
4yes3135
5yes4147
6no51511
7no61613
8yes71717
9yes81819
10yes91923

</tbody>
Sheet3

Worksheet Formulas
CellFormula
F2=SUMPRODUCT(--(A2:A10="yes"),B2:B10+C2:C10-D2:D10)

</tbody>

<tbody>
</tbody>

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.

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.

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.

Replies
4
Views
124
Replies
11
Views
227
Replies
7
Views
106
Replies
2
Views
620
Replies
1
Views
238

1,196,368
Messages
6,014,877
Members
441,853
Latest member
Carlp16

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back