yogi_Sum Values In A Column For A Group Matching Numbers In Another Column

Google Spreadsheet

nomedeusuario said:
I have a situation where I want to have a sumif, where my "condition" is a group and not one variable.
For instance:
Group A:
1
2
3
Activities
A1: 1 B1: 10
A2: 4 B2: 5
A3: 3 B3: 5
A4: 2 B4: 4
A5:5 B5: 5
A6: 1 B6: 1
A7:6 B7: 2
Target:
To build a Sum Group A function that will return in this case 10 + 5 + 4 + 1
Is that possible to achieve that with the Query function ? how ?
--------------------------------------------------------------------------------
In the following I have solutions using the SUMIF function, the FILTER function, and the QUERY function

yogi_Create An Arrayformula That Displays Only Unique Submitters

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

yachdhoo said:
Column B Lists names of user submissions, which continue to grow.
Unfortunately, these do not represent unique users, as some have the same name.
Furthermore, each user submits more than once, and sometimes under a different name each time! (nick name, initials, etc).
Column C is a URL for each user, ending in that user's unique 9-12 digit alphanumeric, as in:
http://www.xxx~~~php?id=123xyz789
(The URL might be different for each user each time, but not the Unique identifier at the end, after the "=")
So, this 9-12 digit alphanumeric is the only way to correctly identify a unique submitter.
Therefore, on another sheet, I'd like to have one arrayformula that results in one row for each unique user (based on the 9-12 digit alphanumeric at the end of the URL they submit) with just two columns of data, auto-populating with additional submissions:
Column A with their names hyperlinked to a specific alternate URL ending in their alphanumeric (hyperlink("http://www.newphp?id=123xyz789","Name Submitted")
Column B with just their unique 9-12 digit alphanumeric, which is at the end of the URL they submit, after a "=").
...and where B has two entries for A, just return the most recent A (if different by way of initials, nickname, fullname, etc...)
Is this possible?
-------------------------------------------------------------------

yogi_Count Objects In An Array Tthat Match Another Array In Different Size

Yogi Anand, D.Eng, P.E.                                 Google Spreadsheet                    www.energyefficientbuild.com

Santiago said:
Count objects in an array that match another array in different size.
I'm working on a dashboard and I have 2 arrays. One with unique values and another one with many values that match the first array. I want to know how many values in the second array match the values in the first array. I know how to do this on VBA, but I'm not allow to use it.
Here is the example:
Array1={a,b,c}
Array2={a,a,a,r,t,y,c,b,b}
here, the result of what I want would be 6 (3 a, 2 b and 1 c)
I appreciate any help.
reference:
http://www.linkedin.com/groupAnswers?viewQuestionAndAnswers=&discussionID=53050779&gid=3124035&commentID=38525014&goback=%2Egde_3124035_member_52807150&trk=NUS_DIG_DISC_Q-ucg_mr#commentID_38525014
------------------------------------------
Following is a solution is Sheet1 using Google Docs Spreadsheet

yogi_ Change Date Text To Date Format

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

PRASAD TR said:
Change Date Text to date format
How can we convert date format
From this
04-12-2011 04:37:00
04-12-2011 12:57:00
04-12-2011 11:37:00
To this
04-Dec-11
04-Dec-11
04-Dec-11
With out doing following things.
#should not copy past in notepad and copy back to excel.
#Should not remove Time.
it's not coming even if do Right click "Format Cells".
Kindly suggest . if past this data in sheet1 it should be automatically change to date format.
reference:
http://www.linkedin.com/groupItem?view=&srchtype=discussedNews&gid=3124035&item=52807150&type=member&trk=eml-anet_dig-b_pd-ttl-cn
---------------------------------------------------------------------------------

yogi_Create BarChart That Sums Values In Column With Similar Labels

Yogi Anand, D.Eng, P.E.                                   Google Spreadsheet                      www.energyefficientbuild.com

rcdailey said:
I have two columns: A and B
In column A, there are dates. e.g.:
5/5/2011
5/5/2011
5/6/2011
5/7/2011
In column B, I have integral values:
50
32
109
76
For each value in column B who's corresponding label in column A is identical, generate a sum of those values and represent that sum in the bar graph. So the result (using the data above) for a bar graph would be:
Label: 5/5/2011
Value: 82
Label: 5/6/2011
Value: 109
Label: 5/7/2011
Value 76
This would result in a bar graph with 3 bars (instead of 4 like you normally would see). How can I do this?
---------------------------------------------------------------------------------------
In the proposed solution here, in Sheet1 I added two columns C and D
in column C I extract unique values from column A, and
in column B I sum up the values in column B  for similarly labeled entries in column A
then I create a Bar Chart fro range C:D

-------------------------------------------------------------------------
I added Sheet1a(WithOpenEndedRanges) to generalize the solution so that when more rows are added tn the data range the BarChart automatically updates.

yogi_Sort Column A InPlace Based On Customized Order For Entries In Column B

Yogi Anand, D.Eng, P.E.                                   Google Spreadsheet                      www.energyefficientbuild.com

RikenJens said:
I'm wondering if this is possible to do. I'd like to create a script to sort data in a spreadsheet by the value in a certain column. The problem is, I want to sort it in a way that is not alphabetical (i.e., there are three values, yes, no, maybe, and I want to sort them as follows: no, yes, maybe. So they are not alphabetical.) Could I assign numbers to the choices, so that 1 = no, 2 = yes, and 3 = maybe, and then sort them numerically? That would make sense, but then would I be able to have 1 be expressed as "no" in the table?
-------------------------------------------------------------------------------
In the following, column A in Sheet1 houses the entries to be sorted by alphabetic entries in column B inthe customized order of first yes entries, then maybe entries, and then no entries. This can be done by adding an intermediary column, say column C (shown in another view of Sheet1 presented here as Sheet1a along with the formula used in cell C1). I have also presented here Sheet1b as another view of Sheet1 where I have reduced the width of the column C, and changed the background color and the font color of column C to yellow.

yogi_Google Form Answers For Multiple Choice Questions

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

GeoBear said:
I just learned how to make an online quiz using Google Docs. So far, it looks great.
But can someone tell me if there's a way to create multiple choice questions with MULTIPLE ANSWERS?
For example,
Question: Which of the following are planets?
Earth
Mars
Africa
Pluto
To correctly answer this question, people would have to check THREE radio buttons (Earth, Mars and Pluto), rather than just one.
Is there a way to do this?
---------------------------------------------------------------------
In the following solution, I used 5 radio buttons for possible answers:
1: Earth only
2: Earth, and Mars only
3: Earth, Mars, and Pluto only
4: all of them
5: none of them


---------------------------------------------------------------------
Here is the Form that responders used to answer the Multiple Choice question

---------------------------------------------------------------------
And following is the Summary of Responses