# conditional sum (search for first cell containing a value)

1. ## conditional sum (search for first cell containing a value)

Hello everyone

I have a row of 5 cells (A1, B1, C1, D1, E1)

I want to put a formula into F1 that will subtract A1 from E1

BUT if A1 is empty I want to subtract B1... and if B1 empty I want to subtract C1... and so on...

Basically I need a formula that will look left to right for the first cell containing a value and subtract that value from E1.

Many thanks

2. ## Re: conditional sum (search for first cell containing a value)

Like this?
Formula:
`Please Login or Register  to view this content.`

3. ## Re: conditional sum (search for first cell containing a value)

Try

=E1-INDEX(A1:D1,MATCH(1,INDEX(--(A1:D1>0),1,0),0))

4. ## Re: conditional sum (search for first cell containing a value)

I believe this formula does it:

=IF(A1>0;E1-A1;IF(B1>0;E1-B1;IF(C1>0;E1-C1;IF(D1>0;E1-D1))))

5. ## Re: conditional sum (search for first cell containing a value)

Originally Posted by bmouse
I believe this formula does it:

=IF(A1>0;E1-A1;IF(B1>0;E1-B1;IF(C1>0;E1-C1;IF(D1>0;E1-D1))))
You could simplify that to

=E1-IF(A1>0,A1,IF(B1>0,B1,IF(C1>0,C1,IF(D1>0,D1))))

6. ## Re: conditional sum (search for first cell containing a value)

Originally Posted by Bob Phillips
You could simplify that to

=E1-IF(A1>0,A1,IF(B1>0,B1,IF(C1>0,C1,IF(D1>0,D1))))

Hey, whatever works, right?

7. ## Re: conditional sum (search for first cell containing a value)

Hey these all look great, thanks!
None of them are returning any values for me though.
Any way of changing it so that it says something like If(A1 is no blank, E1-A1
...or something. Sorry I'm pretty new to excel

8. ## Re: conditional sum (search for first cell containing a value)

WAIT! Looks like it does work! I think I'm just copying it in wrong...gimmee a minute and I'll give credit where credit's due. Thanks everyone :D

9. ## Re: conditional sum (search for first cell containing a value)

Hi Bob, this works perfectly when I make a test model using actually those cell references. For some reason when I try it on my ACTUAL spreadsheet (with the cell references changed) it comes back as #Value!
Should it matter that my A1,B2,C3 etc are not actually next to each other?? I'm really confused as to why it isn't working

10. ## Re: conditional sum (search for first cell containing a value)

Actually, Fotis' formula is not that different but better than mine, as I have an unnecessary coercion, and I test for > 0, he allows for negatives. But even there, it doesn't allow for a true 0, so the best in my view is

=E1-INDEX(A1:D1,MATCH(TRUE,INDEX(A1:D1<>"",0),0))

But getting back to your issue, you never mentioned B2, C3, you said A1:D1. Are you saying that the value are in A1, B2, C3 and say D4? If so, it can be done, but it is more complex

=E1-INDEX(N(OFFSET(A1,{0,1,2,3},{0,1,2,3},1,1)),(MATCH(TRUE,INDEX(N(OFFSET(A1,{0,1,2,3},{0,1,2,3},1,1))<>0,0),0)))

11. ## Re: conditional sum (search for first cell containing a value)

Sorry, I made a mistake in my last message - it has been a long day looking a spreadsheets. I had meant to say A1:D1 (ignore my A1 B2 C2 comment).

What I mean is that I said A1:D1 hypothetically but in actual fact my "A1:D1" are: I4, N4, S4, X4, AC4, AH4,AM4 and my "E1" is: AN4

Using this model =E1-IF(A1>0,A1,IF(B1>0,B1,IF(C1>0,C1,IF(D1>0,D1))))
Surely my formula should be:

=AN4-IF(I4>0,I4,IF(N4>0,N4,IF(S4>0,S4,IF(X4>0,X4,IF(AC4>0,AC4,IF(AH4>0,AH4,IF(AM4>0,AM4)))))))
??

But I can't get it to work. I think I have gone number blind, please guide me across the road to safety

12. ## Re: conditional sum (search for first cell containing a value)

I have just realised why it doesn't work...the cells I'm referencing are not truly blank but contain a formula. How do I get around this??

13. ## Re: conditional sum (search for first cell containing a value)

As BOB previously mentioned you are not clear for 1 more time. Because of your hard day?

Check a small sample with formulas(you didn't mentioned what kind of formula are you using) in A1:D1.

14. ## Re: conditional sum (search for first cell containing a value)

Arghh I'm going crazy. I'm just going to give up for now. Sorry for bothering you all... you have been so helpful and patient!! Unfortunately I cannot upload an example as it is sensitive data. Thanks so much for all your help C:

15. ## Re: conditional sum (search for first cell containing a value)

A time out, looks a nice idea! Tomorrow is another day!

16. ## Re: conditional sum (search for first cell containing a value)

Originally Posted by gjames21
Arghh I'm going crazy. I'm just going to give up for now. Sorry for bothering you all... you have been so helpful and patient!! Unfortunately I cannot upload an example as it is sensitive data. Thanks so much for all your help C:
Create an example and change the data to some made-up names/values.

17. ## Re: conditional sum (search for first cell containing a value)

Originally Posted by gjames21
Sorry, I made a mistake in my last message - it has been a long day looking a spreadsheets. I had meant to say A1:D1 (ignore my A1 B2 C2 comment).

What I mean is that I said A1:D1 hypothetically but in actual fact my "A1:D1" are: I4, N4, S4, X4, AC4, AH4,AM4 and my "E1" is: AN4

Using this model =E1-IF(A1>0,A1,IF(B1>0,B1,IF(C1>0,C1,IF(D1>0,D1))))
Surely my formula should be:

=AN4-IF(I4>0,I4,IF(N4>0,N4,IF(S4>0,S4,IF(X4>0,X4,IF(AC4>0,AC4,IF(AH4>0,AH4,IF(AM4>0,AM4)))))))
??

But I can't get it to work. I think I have gone number blind, please guide me across the road to safety
Your formula works for me, but modifying my suggestion you would use

=AN4-INDEX(N(OFFSET(I4,0,{0,1,2,3,4,5,6}*5,1,1)),(MATCH(TRUE,INDEX(N(OFFSET(I4,0,{0,1,2,3,4,5,6}*5,1,1))<>0,0),0)))

There are currently 1 users browsing this thread. (0 members and 1 guests)