I am an average Excel user, but not to familiar with constructing formulas. I have a spread sheet I wish to sum as follows:
In column A1:A10 is the letter "A" but it is not in every cell, column B1:B10 contain numbers (in all cells) some are positive and some negative. I need to add only the positive numbers in column B1:B10, but only thoses that have the letter "A" in the ajacent A1:A10 column.
Hope this explains it enough for someone to help...thanks before hand.
Re: Cell summing, multi conditions
Your best bet would be to utilise a SUMIF Formula.
e.g. =SUMIF(A1:C10,"AddA",B1:C10)
However as you are using more than 1 condition for the summing I suggest that you insert and additional column into the sheet which includes an if statement along the lines of
=IF(C1>=0,"Add","")&trim(B1)
Re: Cell summing, multi conditions
try this sumproduct formula:
=SUMPRODUCT(($A$1:$A$10="A")*($B$1:$B$10>0)*($B$1:$B$10))
Re: Cell summing, multi conditions
Thank you so very much...the formula worked great!
Re: Cell summing, multi conditions
I used this formula and slightly changed it but it is not skipping the negative numbers, it is summing them with everything else. Please see attached example. Cells D52-L52 have the formulas. Any help would be greatly appreciated.
Thanks! :thanx:
Re: Cell summing, multi conditions
Try...
D52, copied across:
=SUMPRODUCT(--(MOD(ROW(D9:D47)-ROW(D9),4)+1=3),--(D9:D47>0),D9:D47)
Hope this helps!
Re: Cell summing, multi conditions
Thanks for the help. I ended up just writing a formula that checked each cell individually to see if it was positive and then if so added it. Thanks for getting back to me though.
Don’t have an account yet? Register yourself now and be a part of our community!
