Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count across multiple worksheets?
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'!I 9:I15="RD")) but it changes it to =SUMPRODUCT(('1:[31]31'!A9:A15="SILVERDELL")*('1:[31]31'!I9:I15="RD")) Suggestions please Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count across multiple worksheets?
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!* "chrisk" wrote: 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'!I 9:I15="RD")) but it changes it to =SUMPRODUCT(('1:[31]31'!A9:A15="SILVERDELL")*('1:[31]31'!I9:I15="RD")) Suggestions please Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count across multiple worksheets?
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. "Luke M" wrote: 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!* "chrisk" wrote: 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'!I 9:I15="RD")) but it changes it to =SUMPRODUCT(('1:[31]31'!A9:A15="SILVERDELL")*('1:[31]31'!I9:I15="RD")) Suggestions please Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count across multiple worksheets?
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)*(IN DIRECT("'"&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 --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count across multiple worksheets?
This will help with the summary of the summary sheets
Thanks "Luke M" wrote: 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!* "chrisk" wrote: 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'!I 9:I15="RD")) but it changes it to =SUMPRODUCT(('1:[31]31'!A9:A15="SILVERDELL")*('1:[31]31'!I9:I15="RD")) Suggestions please Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count unique email addresses across multiple worksheets? | Excel Discussion (Misc queries) | |||
Count Text within Multiple Worksheets | Excel Worksheet Functions | |||
count 2 nonblank cells on multiple worksheets | Excel Discussion (Misc queries) | |||
Simple Way to Count the Number of Duplicate Dates on Multiple Worksheets | Excel Discussion (Misc queries) | |||
Count # of times value "x" appear across multiple worksheets | Excel Worksheet Functions |