Resolve a problem with a sumproduct formula

**dc123** · 03-14-2008, 11:12 AM

could someone tell me why this doesnt work?

=SUMPRODUCT(--('bakewell home'!$C$2:$O$27="a2")*('bolsover united home'!$C$2:$O$27="A2")*('chesterfield blues home'!$C$2:$O$27="a2")*('glapwell home'!$C$2:$O$27="a2")*('matlock home'!$C$2:$O$27="a2")*('middleton united home'!$C$2:$O$27="a2")*('pilsley home'!$C$2:$O$27="a2")*('rowsley home'!$C$2:$O$27="a2")*('doncaster city home'!$C$2:$O$27="a2")*('glapwell home'!$C$2:$O$27="a2"))

am i right in thinking that this counts how many times a2 is in each sheet (c2:o27)?

**NBVC** · 03-14-2008, 11:17 AM

I don't think you can use Sumproduct this way....

You have to add the individual Sumproducts for each sheet....

actually you only then need Sumif

e.g.

Please Login or Register  to view this content.

**NBVC** · 03-14-2008, 11:21 AM

Another way using a shorter formula would be the following.

First you list all the sheetnames in a vertical array somewhere in your workbook.

Name that array something like "TabNames" through Insert|Name|Define and then you can use formula:

Please Login or Register  to view this content.

This should some the same range in all sheets and give a total

**dc123** · 03-14-2008, 11:26 AM

that doesnt work, it says error,

just to make sure that i have explained myself,
i want to count how many times the cell value (in this case "bowler") appears in the different ranges,

could you help further?

**NBVC** · 03-14-2008, 11:29 AM

What kind of error...if it is #NAME? then you haven't defined the range called TabNames as I mentioned above.

To refer to a cell's contents, just take out the quotes from around A2...

Please Login or Register  to view this content.

EDIT: Sorry that should be COUNTIF:

Please Login or Register  to view this content.

**dc123** · 03-14-2008, 11:46 AM

THE CELL where i want the value to be (where i am putting your formula in) comes out as #REF, do you know why this could be?

**NBVC** · 03-14-2008, 11:49 AM

Did you spell out the sheetnames correctly (or moreover...do they match exactly to the actual tabs - including spaces, etc.)?

BTW: I posted a correction to my previous formula...did you notice it?