Excel vba code or formular

[Ify]

How can I use a vba code or formular to calculate the total payment fo
each customer in Sheet2 based on sheet1 below .
Please note that customers could be up to 2000 with different custome
nos. and for different months.

Example sheet1:

cust no. Months Payment
1 Jun £150
2 Jul £230
5 Jun £300
1 Jun £100
2 Jul £200
5 Jun £300

Sheet2 Has:

cust no. Months Total
1 Jun
2 Jul
5 Ju

 

[muziq2]

Put this formula in the total column:

=SUMPRODUCT((Sheet1!$A$1:$A$2000=Sheet2!A1)*(Sheet1!$B$1:$B$2000=Sheet2!B1),Sheet1!$C$1:$C$2000)


This formula tells XL to look at customer column and the month colum
in sheet 1 and for every row where they match sum the value in colum
C. You'll need to adjust the ranges to accommodate your sheet.

Hope this helps,

Jef

 

[Norman Harker]

Hi Ify!

In Sheet2!C1 use:
=SUMPRODUCT(--(Sheet1!$A$1:$A$6=Sheet2!A1),--(Sheet1!$B$1:$B$6=Sheet2!B1),(Sheet1!C1:C6))
Copy down

The first two arguments are implicit IF functions with TRUE and FALSE
being coerced to 1 and 0 by the --. Only if both are true for a given
row in Sheet1 will you get 1*1*column C entry. SUMPRODUCT adds up the
the sum of these products.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi
in addition to the answers you already have received you may take a
look at pivot tables to create such a summary report:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm