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

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!!!

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

=SUMIF(A2:A100,"0")

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


or


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

depending on if there are other criteria as well


--
Regards,

Peo Sjoblom



"Johnson31d" wrote in message
...
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!!!

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

=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))


Johnson31d wrote:

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!!!

--

Dave Peterson

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

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

"Johnson31d" wrote:

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!!!

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

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!!!


"Dave Peterson" wrote:

=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))


Johnson31d wrote:

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!!!

--

Dave Peterson

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

Try

=SUMPRODUCT(--(A2:A100=A14),--(C2:C1000),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




"Johnson31d" wrote in message
...
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!!!


"Dave Peterson" wrote:

=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))


Johnson31d wrote:

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!!!

--

Dave Peterson

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

THANK YOU!

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

Thanks again!

"Peo Sjoblom" wrote:

Try

=SUMPRODUCT(--(A2:A100=A14),--(C2:C1000),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




"Johnson31d" wrote in message
...
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!!!


"Dave Peterson" wrote:

=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))


Johnson31d wrote:

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!!!

--

Dave Peterson