CODEWITHSUNDEEP
excel
Apolymoxic
Apolymoxic

Reputation: 836

Subtract values of ranged cells from a cell if ranged cells are not blank

First, my apologies for the confusion of this question. The title probably isn't worded very well, but I couldn't think of a better way to ask it. And because I don't know how to ask it simply, I could not find any answers.

I am wondering if there is a way to subtract a value of a cell from another cell if yet another cell is not blank. This is simply done when not using a range using something like:

=if(a1<>"", c1-b1, c1)

But I want to check multiple cells in a range. If a cell in that range is not blank, subtract a correlated value from a working total, otherwise subtract nothing. Go to next cell in the range, if that cell is not blank, subtract a correlated value, and so on...

Let's say:

So... in a1:a10, there will be date. If a1 is not blank, subtract b1 from c1. Lets call this value workingValue. Go to a2... if a2 is not blank, subtract b2 from workingValue. Go to a3... if a3 is not blank, subtract b3 from workingvalue, and on down the list.

The final workingValue can go anywhere, but for simplicity, let's put it in C2.

Is this possible with an excel formula (not VB)?

Upvotes: 0

Views: 1882

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Use this in C2:

=(C1*ROWS(A1:A10))-SUMIF(A1:A10,"<>",B1:B10)

Upvotes: 1

Related Questions