SUMIF function?

  • Thread starter Thread starter ash_eng
  • Start date Start date
A

ash_eng

I'm tring to get excel to do a conditional sum based on the following.
I have two columns, the first containing a reference number, and th
second an ammount which is either positive or negative.

In another sheet I have the reference number and beside it i want
cell to calculte the sum of all the sells with that ref number and tha
contain a positive amount, see the example below.

Ref # Ammount
1 -5.15
1 6.00
1 -3,50
2 2.20
2 -3.40
2 2.40
3 6.40
3 -7.20
3 -1.80


SO then on another sheet i have
Ref Positive Total Negative Total
1 (?) (?)
2 (?) (?)

I need to know what formula to use here, I;ve tried using sumif but
think i need a condition based on two cells, i.e. the ref numbers an
the ammounts.

Any ideas.
Thanks
Ashley.
 
Hi
as SUMIF only supports one condition try the following:
=SUMPRODUCT(('sheet1'!$A$1:$A$100=cell_reference)*('sheet1'!$B$1:$B$100
0),'sheet1'!$B$1:$B$100)
Click to expand...
for the positive amount. Replace cell_reference with your cell
containing the Ref# to compare

For the negative amount use:
=SUMPRODUCT(('sheet1'!$A$1:$A$100=cell_reference)*('sheet1'!$B$1:$B$100
<0),'sheet1'!$B$1:$B$100)

Frank
 
Back
Top