Monday, July 2, 2012

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

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #606   Jul 2, 2012     www.energyefficientbuild.com.


user jkendra said:
COUNTIF across multiple sheets
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.

-----
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

--------------------------------------------------------------
following is a solution to the problem


1 comment:

  1. I'm glad I found this web site, I couldn't find any knowledge on this matter prior to.Also operate a site and if you are ever interested in doing some visitor writing for me if possible feel free to let me know, im always look for people to check out my web site. free word counter

    ReplyDelete