I have a fairly static data page (info is cut and pasted quarterly) and a summary page.

On the summary page, I need data summed by multiple criteria. Each transaction includes the date, salesrep, account, and amount. I need to filter by date, have 3 accounts sum in one place, and 2 in another, and cross reference this by salesrep.

This is part of a much larger sheet, so a pivot table won't quite work, and I can accomplish this using sumproduct, but I would need to repeat the below equation nearly 9 times in a single cell (3 times for each account) - making it quite long and cumbersome.

=SUMPRODUCT(--(invoices!$A$6:$A$2000>=Summary!$B$2),--(invoices!$A$6:$A$2000<=Summary!$B$3),--(invoices!$L$6:$L$2000=Summary!A6),--(invoices!$C$6:$C$2000="Fee income : Quarterly Fee"),invoices!$N$6:$N$2000)

I'm looking for a UDF to complete the task. I got started below with defining some variables and ranges, but I'm not sure where to go next.

Basically, I'll put this equation next to a cell with the sales rep's name, and it will need to find transactions that are
  • between two dates (the first two conditions in the above sumproduct)
  • have the sales rep's name on them
  • match one of three different accounts
if the above conditions are met, go ahead and sum the number in the amount column.

One note - there are a few places on the transaction where a rep's name and amount are listed.

Thanks in advance.

Function recurrings(salesrep As String)

Dim dateclosed As Range, account As Range, amount As Range
Set dateclosed = Sheets("invoices").Range("a6:a2000")
Set account = Sheets("invoices").Range("c6:c2000")
Set amount = Sheets("invoices").Range("h6:h6000")