SUMPRODUCT

[Guest]

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

 

[Bob Phillips]

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)