Is anyone a SUM(IF formula expert (array formulas)?

[Guest]

I am trying to find costs associated with certain project numbers and do two
different things. One, sum the positive numbers, and two, sum the negative
numbers. I have been able to use the array formula SUM(IF(......)).
However, I am having trouble separating the positive from the negative
numbers. I need more than just the net total.... HELP!!!

 

[Peo Sjoblom]

=SUMIF(A2:A100,">0")

=SUMIF(A2:A100,"<0")


or


=SUMPRODUCT(--(C2:C100"Projectx"),--(A2:A100>0),A2:A100)

depending on if there are other criteria as well

 

[Dave Peterson]

=sumif(a1:a10,">"&0)
=sumif(a1:a10,"<"&0)

You may want to add =countif() to do some checking:

=if(countif(a1:a10,">"&0)=0,"No Positives",sumif(a1:a10,">"&0))

 

[Guest]

=SUMIF(A1:A10,">0",A1:A10) Positive Numbers
=SUMIF(A1:A10,"<0",A1:A10) Negative Numbers

 

[Guest]

Thanks for the help so far everyone. I should be more clear. In Column A of
Sheet 2 I have a long list of Project Numbers. In Column C I am trying to
total only the positive amounts from Sheet 1's Column I. But I am trying to
only total those cells that also have the appropriate project number in
Column A as well as being a positive number.

I currently can get the NET Total by using the following formula:

=SUM(IF('Program Spending Detailed'!$A$4:$A$300='Program Actual
Net'!$A14,'Program Spending Detailed'!$I$4:$I$300))

In this formula 'Program Actual Net'!$A14 equals a certain project number...

'Program Spending Detailed'!$A$4:$A$300 equals the range in Sheet 1 with
project numbers...

'Program Spending Detailed'!$I$4:$I$300 equals the range of amounts I am
trying to total (Only positive #s or negative #s)...

If this detail is of any help, I would really appreciate some insight for
this issue.

THANK YOU!!!

 

[Peo Sjoblom]

Try

=SUMPRODUCT(--(A2:A100=A14),--(C2:C100>0),C2:C100)

You can replace your other formula with the much more effective

=SUMIF(A2:A100,A14,C2:C100)


adapt to fit to your sheet names etc

no need using array formulas in these case


--
Regards,

Peo Sjoblom

 

[Guest]

THANK YOU!

This is exactly what I've been trying to figure out all day. This will make
my job much easier.

Thanks again!