Need a formula to count and add

[Guest]

I have a spread sheet that has the columns below.

Customer Ref# Date Total Pd on No. of Dist. Inv. Paid Amt
Inv.(s)
Mary visa123 02/05/06 700 3 101
300
Mary Visa123 02/05/06 700 3 102
200
Mary VIsa123 02/05/06 700 3 103
200
Chad /rregh 01/01/05 300 1 101
300
james ck#123 02/03/06 400 2 101
200
james ck#123 02/03/06 400 2 101
200

I need to be able to count the lines when the ref# and the date are the
same. That figure will go in column “no. of dist.â€, when the “ref#†and
“Inv. Paid†are the same on each of the lines. Then I need to add the “Amt.â€
field total and that figure in the “Total Pd On Inv(s)†column. Hope this
is clear.

 

[Guest]

I have copied your data to range A1:G7, and used the following formulas.
For the No. of Dist, this formula in cell E2:
=SUMPRODUCT(($B$2:$B$7=B2)*($C$2:$C$7=C2))
For the Total, this one is cell D2:
=SUMPRODUCT(($B$2:$B$7=B2)*($C$2:$C$7=C2)*$G$2:$G$7)
And drag both along the column. Change ranges as appropiate.
I couldn't understand what you wanted to do with the inv. paid, sorry.

Hope this helps,
Miguel.

 

[Guest]

What I needed for # of dist. is the count not the value. I figure what I
needed for the total inv part, but it is based off me getting the # of dist.
In other words I need to be able to count how many lines have the same ref#
and the same date and this will give me the "no. of dist". Is this much
clearer? I hope - need to finish this today. I will play around with the
count function?

 

[Bob Phillips]

The first formula that Miguel gave you is a count, the second is the value.
Try them.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

MY BAD!!!!!!!!!!!!!! GUESS I BEEN AT THIS TO LONG. I copy the formula and it
did some but then I had zero's but I did small sample and forgot to change
the range. No I am going to try the value part. Thanks Miguel!!!!!