Logical functions If, And

[GK]

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)

 

[Bob Phillips]

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

and copy down

=SUMPRODUCT(--(A2:A20>0),--(B2:B20>0))

 

[Shane Devenshire]

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 Cost>0 and Paid=0
then
=SUMPRODUCT(--(A2:A7>0),--(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:A7>0),--(B2:B7=0))
or simplify this as above to
=COUNTIF(B2:B7,0)

if this helps, please click the Yes button

Cheers,
Shane Devenshire