Sumif function and negative value together

B

BGN

Hi,

Is there a way to combine these two functions together?

=SUMIF(AP:AP,B4,AS:AS)
=IF(F3<0,J3*F3,J3)

What I am trying to do is to pick up cost from a different table b
using sumif function. But sometimes, there is a return - I want th
sumif function to pick up cost and if it is a negative quantity, I wan
it to reverse the sign.

Thanks for your help.

Regards,
BG
 
B

Bob Phillips

=ABS(SUMIF(AP:AP,B4,AS:AS))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

BGN

Bob,

I don't think making the formula absolute is going to work. Th
formula that I have is picking up the correct sign for all the sales.
I just need it reversed for returned machines.

I need the *IF* function to work after the Sumif function. Afte
Excel picks up my variance from column AS, I want the program to chec
the quantity field. If the quantity is negative one, I want it to jus
change that one cell.

Is it possible to combine the two functions together?

Thanks for your help.

Regards,
BG
 
B

Bob Phillips

I can't say that I understand which value could be negative, so this is a
total guess

=SUMIF(AP:AP,ABS(B4),AS:AS)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

BGN

=SUMIF(AP:AP,B4,AS:AS)
=IF(F3<0,J3*F3,J3)


The sumif function is in column J. So generally, I put in the I
function in column K. Then I copy paste the entire column K as value
in column L. Quantiry is in column F.

I need to repeat the process quite a few times to get the cost
variance, and other details lined up for each machine. If I ca
combine these functions, it will save me some time each month.

Is there anyway I can attach a portion of my spreadsheet, so you ca
see my question?

I really appreciate all your help and the website. It is really nice.

Thank you.
BG
 
B

BGN

A collegue helped me with my question. Here is the answer if anyone i
interested:
=IF(F3<0,SUMIF(AS:AS,B3,AV:AV)*F3,SUMIF(AS:AS,B3,AV:AV))

Thank you

BG
 
Top