Need a formula to count and add

[klafert]

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.

[Miguel Zapico]

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.

"klafert" wrote:

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

[klafert]

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?


"Miguel Zapico" wrote:

> 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.
>
> "klafert" wrote:
>
> >
> > 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.
> >

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

"klafert" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>
> "Miguel Zapico" wrote:
>
> > 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.
> >
> > "klafert" wrote:
> >
> > >
> > > 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.
> > >

[klafert]

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


"Bob Phillips" wrote:

> 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)
>
> "klafert" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> >
> >
> > "Miguel Zapico" wrote:
> >
> > > 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.
> > >
> > > "klafert" wrote:
> > >
> > > >
> > > > 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.
> > > >
>
>
>