yogi_Compute Sum Of Entries In Specified Columns Containing Both Numeric And Text Entries

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


user emailgarvin said:
IFERROR( sum) bug producing incorrect result.
The bug specifically does not add some columns together.
The formula of the IFERROR( sum) bug producing incorrect result.
If you please look at the second sheet called "Records" the formula in question relates to the first sheet called "Found":
=IFERROR( sum(Found!B6:B))+ IFERROR( sum(Found!L6:L))+ IFERROR( sum(Found!V6:V))
The formula adds from above (10+10)+(10+10)+(10+10)=60
however produces 40, Specifically the last section of formula is not being added:
i.e. + IFERROR( sum(Found!V6:V))
The public spreadsheet is here and editable.
Thank you for your time and reolving this bug.
----------------------------------------------------------------------------------
to my knowledge there is no bug 
have a look at a couple of formulations presented herein as a solution to the problem

yogi_Display Each Employee And Hours Worked From A Table Consisting Of Multiple Names And Their Hours

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


user benomatic42 said:
UNIQUE failure, plus conditional sums
Hi hi,

I am trying to work a fairly simple spreadsheet into simple, per-person counting.  I have data like this:

  https://docs.google.com/spreadsheet/ccc?key=0AvDqLN02sJwJdEc2bTZWS2FZVGJuSWVfSmhvR1dNaVE

Basically, 2 columns.  1st column is of comma separated strings, 2nd is a count.

[ Ben           | 3 ]

[ Ben, Tim      | 5 ]

[ Tim, Fred     | 2 ]

[ FRED, GEORGE  | 7 ]

What I seek is a summary of the count (hours), per unique person.  It could (but need not) look like:

[ ben    | 8 ]

[ fred   | 9 ]

[ george | 7 ]

[ tim    | 7 ]

My first function, to separate and uniquify the names, is my first failure:

  =UNIQUE(SPLIT(LOWER(JOIN(" ";F2:F20)); " ,;"))

The unique seems to do nothing. I also tried to use SORT on it, but that also failed to change the result. Suggestions?

Thanks,

ben

-------------------------------------------------------------------------------------------------------

following is a solution to the problem

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

yogi_Compute Distance Between Sets Of 2 Postcodes In United Kingdom

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


user teadby said:
How do i calculate driving distance in miles from postcode to postcode in spreadsheet?
I want to automatically calculate the diving distance between a list of 2 United Kingdom postcodes in Google spreadsheet.

Column A contains the FROM postcodes and Column B contains the TO postcodes. I want the results to appear in column C in miles

There are column headers so the FROM codes start in A2 continuing down and the TO codes start in B2.

i want the distance in miles results for A2 to B2, A3 to B3, A4 to B4 etc etc in an array that continues down each row of column C that has an entry in the TO column (column B) in that row.

Is there a way of doing this? Using Google maps maybe? 

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

yogi_Display Commission Earned By Viewing Team Member Only Without Giving Team Members The Right To EDIT Spreadsheet

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

I created this illustration in response to a comment by use KECSS in my following blog post

http://yogi--anand-consulting.blogspot.com/2012/06/yogidisplay-commission-earned-by_25.html

the idea is to display the viewing Team Member's Commission history in a web page without having to give the viewing Team Members the right to EDIT the spreadsheet.

here is an image of the Commission Data1

.

and in the following I show the setup of the Form for SUBMITting Passcode by the Viewing Team Member and then viewing upto 10 rows of Commission for the SUBMITted Passcode
  here is the Form via which Team member SUBMITs her/his Passcode and then after SUBMITting the Form refreshes the browser to view upto 10 rows of Commission data

or you may make the spreadsheet available only for View by hiding all the other sheets except the sheet CommissionLookupByPasscode(TextOrNumeric)1

yogi_Setup Formula For Multi_Condition Sum That Works In Google Spreadsheet As Well As In Excel And OpenOffice Spreadsheet

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


user smithbc said:
Is there an Array or Sumifs formula that will work in a Google Spreadsheet and in Open Office / Excel?
MAC OSX - 10.6.8

Chrome - Version 20.0.1132.47

Open Office 3.3.0

I am creating a budget spreadsheet that I would like to be used online and offline. I have software that allows me to sync my spreadsheets with Google Docs and edit them online or offline (www.insynchq.com). Ultimately what I want to do is allow users of the spreadsheet to edit online and offline, but it appears that you cannot edit a google spreadsheet offline, AND it appears that Open Office and Google Spreadsheets speak different formula languages when it comes to complicated formulas. 

On my computer I am using Open Office 3.3.0. I am trying to create a SUMIFS or similar formula to run some calculations. I have found the Google Spreadsheet formulas that are alternatives to SUMIFS and implemented those successfully. However, when I download the Google Spreadsheet into Open Office, I get errors where the formulas are. I would like to know if there is a formula that both Google Docs and Open Office and Excel would recognize. 

The formula I'm using is:

=IFERROR(IF($G10<>"";SUM(FILTER($E$3:$E;$A$3:$A=$G10;$D$3:$D=H$9));0))

What the formula is doing is looking for a date (G10) in a list of expense entries by date (A3:A). It is also looking for a category (H9) in the same list of expense entries (D3:D) and when those two match it is summing the amounts (E3:E) of the expenses that meet those criteria. 

You can see the spreadsheet here:

https://docs.google.com/spreadsheet/ccc?key=0AgR7Y_5VHTP6dDh4QnI2VXpsbW5sNmxzNm9KTW9Dc0E

Thank you for any help you can offer.

-------------------------------------------------------------------------------------

in the following solution to the problem, in Sheet1 I have set up the formula for multi_conditional sum that works in Google spreadsheet, as well as in Excel and Open Office spreadsheet

yogi_Merge Data Form Various Weekday Sheets Into A Single WholeWeekSheet

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


user Roee Adler said:
Google Spreadsheet: aggregating rows from multiple sheets
In a single spreadsheet, I have several sheets containing data that is structured in the same way. I would like to create a new sheet that will dynamically include all the rows from several other sheets. 

I'll give an example. Say I have a spreadsheet that contains one sheet per weekday (SundaySheet, MondaySheet, etc). Each of those sheets has exactly the same columns (e.g. "Item", "Count", "Cost Per Item", "Total"). What I'd like to do is create a new sheet called "WholeWeekSheet", that'll include all the rows from all other sheets, such that if I add a row to one of the sheets it will automatically be added to this aggregated sheet. 

Is that at all possible?

--------------------------------------------------------------------------------------------

following is a solution using the VMERGE (custom written function by ahab) available in Script gallery from within Google spreadsheet