Sumproduct help

[Walter Mayes]

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

 

[Bob Phillips]

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]

Bob

No spaces in formula and have looked at both a hundred times. Cannot
find anything unusual. Would you care to take a look?

Walter Mayes

 

[Roger Govier]

Hi Walter

It looks to me as though you have a parenthesis in the wrong place
Your formula starts with 3 opening parentheses, and only has a one
parenthesis for the final term
'wkly meter totals'!$c$4:$bb$41)

Try amending to

=sumproduct(
('wkly meter totals'!$a$4:$a$41=a6)*
('wkly meter totals'!$c$4:$bb$4=j6)*
('wkly meter totals'!$c$4:$bb$41))

 

[Walter Mayes]

Roger

I do have two parenthesis at the end. A total of 4 in and 4 out.
I have tried playing with the parenthesis and Excel tells me I have a
mistake and rearranges the parenthesis but will still get an error of some
kind when Excel does this.I have "cleared all" in case something is in a
cell but not showing. I have tried changing the ranges. Still no luck. I am
totally stuck/confused. My lack of Excel knowledge is showing. 2006 book
works fine.

If anyone on this group would care to look at the workbooks, I'd be
happy to send them and would appreciate any assistance.

Thanks
Walter Mayes

 

[Bob Phillips]

Yeah, send it on over.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Roger Govier]

Hi Walter

You are misunderstanding what I said. I agree that your number of
parentheses are correct in total.
Yes you do have 2 at the end.
But it should be
=SUMPRODUCT( )
with
('wkly meter totals'!$a$4:$a$41=a6)*
('wkly meter totals'!$c$4:$bb$4=j6)*
('wkly meter totals'!$c$4:$bb$41)

between the sumproduct parentheses

What you have is
=SUMPRODUCT(( )
with
('wkly meter totals'!$a$4:$a$41=a6)*
('wkly meter totals'!$c$4:$bb$4=j6)*
'wkly meter totals'!$c$4:$bb$41)

If that amendment doesn't sort it out for you, feel free to email me the
workbook.
Remove NOSPAM from my email address to mail direct

 

[Walter Mayes]

Tried that but no luck. I'll send you the books. I have also sent the
books to Bob.

Thanks
Walter Mayes

 

[Roger Govier]

Hi Walter

Workbook returned.
Problem caused by invalid cell reference on Weekly Meter Totals sheet
giving rise to #REF errors, being pulled through into Sumproduct
formulae.

Whilst your formula was syntactically incorrect, the last term of the
expression was summing values as opposed to testing against a cell value
and didn't give a problem. I have, however, amended all the formulae to
the way they should be set.

 

[Bob Phillips]

The problem is on Weekly Meter Totals. there are many #REF errors there that
need to be resolved. The formulae that aren't #REF there seem to be pointing
at the wrong cells (H5, I5, etc), should be $A5 etc.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Walter Mayes]

Thanks to Roger and Bob.

I didn't realize those errors on the Weekly Meter Totals sheet would affect
the other sheet as I was only dealing with sample data for the first couple
of weeks in 2007. I would have never thought to check where you did. When
the 2006 sheets were made, I made the sheets (in systematic order) and
everything worked.
Thanks to both you I have learned that the computer thinks differently
from me.

Thanks again
Walter Mayes

 

[Roger Govier]

Hi Walter

You're very welcome. Thanks for the feedback and a very Happy New Year.