Excel vba code or formular

  • Thread starter Thread starter Ify
  • Start date Start date
I

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