Categories: Sheets : Ask a "how-to" question :

COUNTIF across multiple sheets

Showing 1-6 of 6 messages
COUNTIF across multiple sheets jkendra 7/1/12 1:17 PM
I have a spreadsheet I am using as a specialized work calendar.  Each sheet is a one week date range.  I typically have 6 weeks (sheets) per worksheet.  Each sheet is of the same format.  Each sheet is named for its week, ie., "June 24-30"
The last sheet is a summary page where I can keep track of schedule requests and make sure shifts are evenly distributed.
I would like to count the number of times "employee1" and "employee2" names show up in a certain cell range across multiple sheets.

I recently tried the excel formula:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&TabNames&"'!B3:B7"),"A2"))

where TabNames is a column of my 6 sheet names, B3:B7 is the row I want to search in each sheet and A2 is the employee's name, but this doesn't seem to work in Google spreadsheets.  Can someone come up with an alternative formula that will work in Google?

I had been using the function "ThreeDim" in the script, "Merge ranges across sheets", which worked - but sporadically.  It doesn't seem to update in realtime, and even when I resave the script, as suggested, it doesn't always update the data appropriately.


Thanks.


Re: COUNTIF across multiple sheets Yogi Anand 7/1/12 3:18 PM
Hi jkendra:

one way would be to use the VMERGE custom written function by ahab (available in Script gallery from within a spreadsheet) to merge the data from various weeks into the summary sheet -- then you can use a function such as FILTER or QUERY to extract the information of interest

if you need to discuss this further, I suggest you share your spreadsheet with some sample but realistic data along with your projected results

and then let us take it from there.

Cheers!
Yogi
Cloud Computing -- Google Docs Way
Re: COUNTIF across multiple sheets jkendra 7/1/12 7:50 PM
Yogi,

If you could show example of script that would be great!
Maybe after looking at example spreadsheet you may have another idea...
Example spreadsheet has different data than that referenced to in my initial post but idea is same.

https://docs.google.com/spreadsheet/ccc?key=0AnZsDYRGkN2-dEpISXh2MmxlOUcxU3U5Wkd1d3FvR0E


Re: COUNTIF across multiple sheets Yogi Anand 7/2/12 11:25 AM
Hi jkendra:

I have posted a solution in my following blog post ...

yogi_Count Number Of Employees At A Specified Location From Data In Multiple Sheets

Please check it out to see how this works for you.
Re: COUNTIF across multiple sheets jkendra 7/2/12 4:16 PM
Thanks for your help, but I am currently using:
=countif('July 2-8'!C$6:G$6;A4)+countif('July 9-15'!C$6:G$6;A4)+countif('July 16-22'!C$6:G$6;A4) 

which essentially does the same thing.  What I would like to do is either
1- optimally not have to include the sheet names so I can add them or delete them without affecting the function
2- or at the least, have the sheet names included in the formula as a named range, or referenced in another column of cells that include the names.

If I have only 4 weeks done, then add two more weeks, I have to redo all the formulas.  Also, this is only one of 5 counting formulas I use for 12 employees so I am trying to increase the amount of automation.

Also, could I request you remove my email from the spreadsheet on your blog site - it's included with all the comment markups.
Thanks again for your help.  If you have any other suggestions they would be welcomed.

jkendra.
Re: COUNTIF across multiple sheets AD:AM 7/2/12 5:32 PM
+jkendra, there really is no "easy" solution for this.  The problem with custom functions (like the ThreeDim function you've attempted) is that they will not automatically recalculate with each edit, unless the values that are being processed are being passed directly in the function parameters (rather than a string for the range and a sheet index number), which means specifying each sheet range directly, which kind of defeats the purpose for the reasons you mentioned.

You can put a dummy argument in the custom function like GoogleClock() which should force the function to recalculate once each minute (but not on each edit).

But really, custom functions simply don't work all that well in this situation.  I'm thinking of a script solution where there might be a custom menu that allows you to create names for "3-D" ranges, which would actually be populated in a hidden sheet using formulae which would be constantly updated... but I'll need a bit of time to think it through I think. :-(