Logical functions If, And
 

I am trying to do two things.

1). create a formula that compares one column of data to another. If the
cost is greater than zero and the amount paid is = to zero, then I want it to
sum the variances.

2). create a formula that provides the number of occurances of number 1
above. ie. how many times is ther a cost with payment made.

Here is my table:

Cost Paid Variance
250 250 0
275 0 (275)
0 0 0
175 175 0
275 55 (220)
175 0 (175)

--
Thanks,
GK

Logical functions If, And
 

=IF(AND(A20,B2=0),B2-A2,0)

and copy down

=SUMPRODUCT(--(A2:A200),--(B2:B200))

--
__________________________________
HTH

Bob

"GK" wrote in message
...
I am trying to do two things.

1). create a formula that compares one column of data to another. If the
cost is greater than zero and the amount paid is = to zero, then I want it
to
sum the variances.

2). create a formula that provides the number of occurances of number 1
above. ie. how many times is ther a cost with payment made.

Here is my table:

Cost Paid Variance
250 250 0
275 0 (275)
0 0 0
175 175 0
275 55 (220)
175 0 (175)

--
Thanks,
GK

Logical functions If, And
 

Hi,

Your description and your sample data don't agree. What's happening on the
5th row of data? The cost is greater than 0 but the amount paid is not = 0.
So according to your discription this should be ignored but you put in a
variance? Which way do you want it?

Suppose that the data is in cells A1:C7

The sum of the variance is =SUM(C2:C7)
The count of the variance is =COUNTIF(C2:C7,"<0")

You could also use "0" in the second one but to be on the safe side I
tested for positive or negative variances.

If on the other hand you want the sum of the variance when Cost0 and Paid=0
then
=SUMPRODUCT(--(A2:A70),--(B2:B7=0),C2:C7)
however, this can be simplified if you never have a negative cost (seems
reasonable)
=SUMIF(B2:B7,0,C2:C7)
To count the occurance you could use
=SUMPRODUCT(--(A2:A70),--(B2:B7=0))
or simplify this as above to
=COUNTIF(B2:B7,0)

if this helps, please click the Yes button

Cheers,
Shane Devenshire

"GK" wrote:

I am trying to do two things.

1). create a formula that compares one column of data to another. If the
cost is greater than zero and the amount paid is = to zero, then I want it to
sum the variances.

2). create a formula that provides the number of occurances of number 1
above. ie. how many times is ther a cost with payment made.

Here is my table:

Cost Paid Variance
250 250 0
275 0 (275)
0 0 0
175 175 0
275 55 (220)
175 0 (175)

--
Thanks,
GK