|
Need a formula to count and add
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. |
Need a formula to count and add
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. |
Need a formula to count and add
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. |
Need a formula to count and add
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" wrote in message ... 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. |
Need a formula to count and add
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" wrote in message ... 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. |
| All times are GMT +1. The time now is 11:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com