SUMPRODUCT
 

Hi,

I have two worksheets "Expenses" and "Totals"
In "Expenses" Column A = Month
Column B = Name
Column E = Amount

In "Totals" Column A = Name
Col B-M are the months.

I would like the month columns in the "totals" sheet to reflect the total
"Amount" in "expenses" based on Month (Column A "Epenses") and the name in
Column A of "totals"

i use eg
=SUMPRODUCT(--(Expenses!A2:A6="September"),--(Expenses!B2:B6=A18),--(Expenses!E2:E6))

This works However

How do you set the formula up so that when new rows are added for workers
the formula can be copied?

and

How do you set the formula up so that when new rows are added in expenses
the totals change? So E2:E6 becomes E2:7 etc.

I think i have made sense

SUMPRODUCT
 

Just use a larger range

=SUMPRODUCT(--(Expenses!$A$2:$A$2000=B$1),--(Expenses!$B$2:$B$2000=$A2),Expe
nses!$E$2:$E$2000))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hardy" wrote in message
...
Hi,

I have two worksheets "Expenses" and "Totals"
In "Expenses" Column A = Month
Column B = Name
Column E = Amount

In "Totals" Column A = Name
Col B-M are the months.

I would like the month columns in the "totals" sheet to reflect the total
"Amount" in "expenses" based on Month (Column A "Epenses") and the name in
Column A of "totals"

i use eg

=SUMPRODUCT(--(Expenses!A2:A6="September"),--(Expenses!B2:B6=A18),--(Expense
s!E2:E6))

This works However

How do you set the formula up so that when new rows are added for workers
the formula can be copied?

and

How do you set the formula up so that when new rows are added in expenses
the totals change? So E2:E6 becomes E2:7 etc.

I think i have made sense