How to count across multiple worksheets?

[chrisk]

In column A is the client or a day off
in column I is the employee (RD)
there are 31 worksheets
I want to count how many days off RD has had.
or how many jobs for silverdell he has done by changing the criteria cell.
I've tried various other counts/if and sum attempts but I always get #ref
My last attempt was
=SUMPRODUCT(('1:31'!A9:A15="SILVERDELL")*('1:31'!I9:I15="RD"))
but it changes it to
=SUMPRODUCT(('1:[31]31'!A9:A15="SILVERDELL")*('1:[31]31'!I9:I15="RD"))
Suggestions please
Thanks

 

[Luke M]

Sadly, SUMPRODUCT can not be used with 3D references. You can do it in 2
steps however, as SUM supports 3D references.
Select sheets 1:31 and in a cell you know is blank (lets say Z1) in all of
them, enter
=SUMPRODUCT((A9:A15="SILVERDELL")*(I9:I15="RD"))
You could hide this column if you want, for looks.

Then on your summary page, simply have
=SUM('1:31'!Z1)

 

[chrisk]

Is it possible to do it the other way round?
I would like to change the 'employee' and 'client/day off' on the summary
sheet so that I can change it for each person and client.
Will index/match or count or vlookup work with the 3D reference?
I don't know what 3D refs are or how they work.

Sadly, SUMPRODUCT can not be used with 3D references. You can do it in 2
steps however, as SUM supports 3D references.
Select sheets 1:31 and in a cell you know is blank (lets say Z1) in all of
them, enter
=SUMPRODUCT((A9:A15="SILVERDELL")*(I9:I15="RD"))
You could hide this column if you want, for looks.

Then on your summary page, simply have
=SUM('1:31'!Z1)

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

In column A is the client or a day off
in column I is the employee (RD)
there are 31 worksheets
I want to count how many days off RD has had.
or how many jobs for silverdell he has done by changing the criteria cell.
I've tried various other counts/if and sum attempts but I always get #ref
My last attempt was
=SUMPRODUCT(('1:31'!A9:A15="SILVERDELL")*('1:31'!I9:I15="RD"))
but it changes it to
=SUMPRODUCT(('1:[31]31'!A9:A15="SILVERDELL")*('1:[31]31'!I9:I15="RD"))
Suggestions please
Thanks

 

[Max]

One way to frame it up easily - use INDIRECT to provide this flexibility:

I would like to change the 'employee' and 'client/day off' on the summary
sheet so that I can change it for each person and client.

In your summary sheet,
In C1 across, list all the source sheetnames, eg: 1,2, ... 31
In A2:B2 down will be the paired input variables, eg: Silverdell, RD
Place in C2
=SUMPRODUCT((INDIRECT("'"&C$1&"'!A9:A15")=$A2)*(INDIRECT("'"&C$1&"'!I9:I15")=$B2))
Copy C2 across/fill down as far as required. This returns the required
summary count figs from each source sheet. Then just use a simple SUM formula
in a col to the right to add it up row-wise, eg in AH2, copied down:
=SUM(C2:AG2)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---

 

[chrisk]

This will help with the summary of the summary sheets
Thanks

Sadly, SUMPRODUCT can not be used with 3D references. You can do it in 2
steps however, as SUM supports 3D references.
Select sheets 1:31 and in a cell you know is blank (lets say Z1) in all of
them, enter
=SUMPRODUCT((A9:A15="SILVERDELL")*(I9:I15="RD"))
You could hide this column if you want, for looks.

Then on your summary page, simply have
=SUM('1:31'!Z1)

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

In column A is the client or a day off
in column I is the employee (RD)
there are 31 worksheets
I want to count how many days off RD has had.
or how many jobs for silverdell he has done by changing the criteria cell.
I've tried various other counts/if and sum attempts but I always get #ref
My last attempt was
=SUMPRODUCT(('1:31'!A9:A15="SILVERDELL")*('1:31'!I9:I15="RD"))
but it changes it to
=SUMPRODUCT(('1:[31]31'!A9:A15="SILVERDELL")*('1:[31]31'!I9:I15="RD"))
Suggestions please
Thanks