Sumproduct..need a push in the right direction

  • Thread starter Thread starter Walter Mayes
  • Start date Start date
W

Walter Mayes

I have a sheet (Register01532) that has:

B C E
Dates Payee Amount

I need to pick up the total amounts for each payee in each month. Same
payee may appear more than once each month at different dates. January
entries begin on B7 and end on B27

What I tried first:
SUMPRODUCT(('register01532'!$B$7:$B$128>='register01532'!$B$7)*('register015
32'!$B$7:$B$128<=$B$27)*('register01532'!$C$7:$C$128=$a7)*'register01532'!$E
$7:$E$128)

A7 in the above formula is referring to a list of all the payees on
SHEET2. This worked until I discovered there was a January entry below B27.
The above formula did not pick up the entry that was out of sequence because
it is an absolute reference to B27. I don't feel I can change the original
entries/sort by date, as this would skew the balance column.
Next I tried:

SUMPRODUCT((('register01532'!$B$7:$B$128>=DATEVALUE("02/01/04"))*('register0
1532'!<=DATEVALUE("02/29/04"))*('register01532'!$C$7:$C$128=$A7)*'register01
532'!$E$7:$E$128)))

I was thinking the above would pick out all monthly dates regardless of
where they were entered, however, I am getting a #VALUE error with the above
formula.
I would appreciate a little push in the right direction.

THANKS
Walter Mayes
 
You've got a couple of syntax errors. This works for me with:

=SUMPRODUCT((register01532!$B$7:$B$128>=DATEVALUE("02/01/04"))*(register0
1532!$B$7:$B$128<=DATEVALUE("02/29/04"))*(register01532!$C$7:$C$128=$A7)*
register01532!$E$7:$E$128)

or

=SUMPRODUCT(--(register01532!$B$7:$B$128>=DATEVALUE("02/01/04")),
--(register01532!$B$7:$B$128<=DATEVALUE("02/29/04")),
--(register01532!$C$7:$C$128=$A7), register01532!$E$7:$E$128)
 
Thanks JE. Your formula works and I found my #VALUE error. However, I'm
not getting what I thought I would and I can see why but can't find a
solution.
I wanted to go through each month in the year picking out the totals for
each payee, by the month. January works well but February totals both Jan.
and Feb. and I can see why. DATEVALUE<=02/29/04 "would" include everything
prior to 2/29/04.
Saved my spreadsheet under a different name and rearranged my dates so
that they were sequential and "my" first formula works but I don't think
this is a wise move because of the way the actual entries are made.
Any suggestions?

Walter Mayes
 
Back
Top