How to sum adjacent cells based on values of each cell?
 

Good day to all!
I'm hitting a brick wall in trying to set up a formula in Excel so I've come for help. It seems like it should be an easy =sumif or If/then formula but haven't been able to accomplish yet.
I can't see where I'm able to insert a picture or print screen of what I have, so I'll just use a very basic example.
Here are the values in my cells:
A1=0
B1=40
A2=10
B2=15
A3=30
B3=20
A4=10
B4=0


What I want to do is have an auto sum in cell B5 that does the following:
Add the total of column B amounts, only if greater than 0, but only if the cell in column A has an amount greater than 0 also, in the adjacent cell. For example, B2 would be added in the sum because A2 has an amount greater than 0 also. Cell B3 would also be added because cell A3 has an amount greater than 0 also. B1 and B4 would not be added in the total sum because cell A1 is 0 and cell B4 is 0. So my total in cell B5 would be 35.
Basically, just adding column B amounts only, and only if column B row has an amount AND column A of the same row has an amount.
I hope that makes sense!
Looking forward to hearing from anyone who can help with this.
Thanks!

How to sum adjacent cells based on values of each cell?
 

hi,

Am Mon, 13 Oct 2014 19:19:32 +0100 schrieb redlegend:

A1=0
B1=40
A2=10
B2=15
A3=30
B3=20
A4=10
B4=0

a 0 in column B does NOT change the sum and so it must not be ignored.
Try:
=SUMIF(A1:A4,"0",B1:B4)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

How to sum adjacent cells based on values of each cell?
 

Hi,

Am Mon, 13 Oct 2014 20:54:07 +0200 schrieb Claus Busch:

=SUMIF(A1:A4,"0",B1:B4)

if there can be values < 0 in column B then try:
=SUMIFS(B1:B4,A1:A4,"0",B1:B4,"0")
or
=SUMPRODUCT(--(A1:A40),--(B1:B40),B1:B4)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

That did the trick! I was close, but forgot about inserting "". Thanks so much!

Does Excel give us the option to sum amounts in a column based on the amount being greater than 0, and if the cell is highlighted a certain color? For example, if cells A2 and A4 were highlighted, is there a way to add just those two cells from that column?