subtract and add cells of a column meeting criteria

lethargos

New Member
Joined
Dec 16, 2016
Messages
3
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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.
 
Upvote 0
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.
 
Upvote 0
I just mentioned SUMIFS as a possibility until I had more information about your requirements. Here's one possibility that might work for you:
ABCDEF
1CriteriaCol 2Col 3Col 4
2yes111251
3no2123
4yes3135
5yes4147
6no51511
7no61613
8yes71717
9yes81819
10yes91923

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3

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

<thead>
</thead><tbody>
</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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top