Could it just be that the formula split badly in the transposition?

Maybe try

=SUMPRODUCT((('wkly meter totals'!$A$4:$A$41=A6)*
('wkly meter totals'!$C$4:$BB$4=J6)*'wkly meter totals'!$C$4:$BB$41))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Walter Mayes" wrote in message
...
Am converting my workbooks over from 2006 to 2007. Hope I can
explain this clearly.

Saved my 2006 workbook as 2007 so it should be a simple matter to
delete certain 2006 data and change the dates. The below Sumproduct
formula works in the 2006 sheet but I am getting a #REF error in the 2007
sheet.

In the (Ticket Mach.) sheet I have the following formula:

=sumproduct((('wkly meter totals'!$a$4:$a$41=a6)*('wkly meter
totals'!$c$4:$bb$4=j6)*'wkly meter totals'!$c$4:$bb$41)) This works in
the 2006 sheet and my answer is $57.00 This formula is dragged down and
works for the whole year for each game.

This same formula in the 2007 sheet gives me a #REF error. When I click
on the error arrow it says "Invalid Cell Ref. error." When I click "Edit
in formula bar", a6 is highlighted.

Formula in Ticket Mach. sheet ( both years) is in cell k6
Types of data in both years is the same.
Wkly Meter Totals:
a4:a41 is text (names of games)

Ticket Mach.
a6 is names of games

Wkly Meter Totals
c4:bb4 are dates (every Monday)

Ticket Mach.
j6 is a date

Wkly Meter Totals
c4:bb41 are numbers

What it should do: Look down Column A in "Wkly Meter Totals" and find
the same name that is in "Ticket Mach." a6, then look across row c4:bb4 in
"Wkly Meter Totals" to find the same date that is in "Ticket Mach." j6,
then return the number that is found in "Wkly Meter Totals" c4:bb41

The names and numbers that are in the "Wkly Meter Totals" sheet are
being picked up from a previous sheet and there are no problems with that
I have copied and pasted the names to assure of correct spelling..

I have checked the formatting, (general) and (dates) and cannot locate
what the problem may be. Any assistance would be appreciated. Would be
willing to send both books, should anyone request them.

Thanks
Walter Mayes