yogi_Count The Entities And Their Related Sums By Month And Year

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

user Kelemvor said:

How can I tally up items from one sheet into a summary on another?

Sample: https://docs.google.com/spreadsheet/ccc?key=0Ajg2ipGcRDpSdDNTazJHTXFZMHUyeUZ0Z1ozRG5sRWc
OK, so I got the totally of the items to work via this: 
=SUM(FILTER( Data!C$2:C ; (Data!E$2:E)=A3 ; YEAR(Data!A$2:A)=2012 ))
I took the month portion of the date in Data!A2 and put that in Data!E2 and then compared that to the text in the summary sheet.
Probably an easier way so let me know but it's at least working for now.
However, I haven't gotten the count part to work.  All I want to do is count how many entries there are for each month/year.
I tried this:
=COUNTIF ((Data!E$2:E100),A5 )  But it didn't work.
Column E in the Data sheet contains the months of the dates (January, February, etc)
Cell A5 in the Summary sheet contains the word August.
So if there are any entries in Data!E that say August, it should count them...  But it just shows me 0.
hmmmm
----------------------------------------------------------------------------------------------
following is a solution using the QUERY function

yogi_Cross Tab Comments By Store Name In Sheet Named Survey Comments From Sheet Named Form Data

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #601   Jun 28, 2012     www.energyefficientbuild.com.

user Grumpathy said:
Copying a column on data using another column as a criteria
I would like to find a way to pull all data from a column IF the data in another column matches a specific criteria. 

As an example:

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

I would like to pull the comments ('Sorted Data' column F) from a specific store ('Sorted Data' column A) and list them all together, so that I get all the survey comments from New York, all the survey comments from Atlanta, etc. 

Is there any way to do this?

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

in the following solution to the problem I have cross tabbed the comments directly from Form data sheet into Survey Comments sheet

yogi_Rearrange In Sheet2 Unique Data In Columns Of A Table In Sheet1 By Entity In First Column Of The Table

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #600   Jun 27, 2012     www.energyefficientbuild.com.

I created this blog post in response to a comment by user KECSS Discipline (Nancy) in my following blog post http://yogi--anand-consulting.blogspot.com/2012/06/yogirearrange-unique-data-in-columns-of.html?showComment=1340834707048#c3882690451522609322
 

yogi_Display Commission Earned By Viewing Team Member Only And Not Other Team Members2 Where Passcode is Text Or Numeric

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #599   Jun 25, 2012     www.energyefficientbuild.com.

this post is to cover a more general condition for the Passcode, being Text or Numeric in contrast to just being a text entry for which I had provided a solution in my following blog post:
http://yogi--anand-consulting.blogspot.com/2012/06/yogidisplay-commission-earned-by.html
so the following solution covres the case regardless whether the Passcode is Text or Numeric
first let me show an image of the Commission Data spreadsheet

yogi_Track Multiple Accounts By Date -- Provide Stats In Another Sheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #598   Jun 25, 2012     www.energyefficientbuild.com.

user AYdstie said:
Tracking multiple accounts by month using SUMIFS?

I am trying to move all of my work documents over to GoogleDocs. I am having a hard time getting this document and it's formulas to transfer over. I basically need to pull the information from one worksheet on to another. I need to track by month, and account type. It's hard to describe so I included the link to my document. If you are able to help I'll grant access.

https://docs.google.com/spreadsheet/ccc?key=0AqmvLMX1nlpLdDNBc29ZNkNic0loR2x1RWxJcF9hUnc#gid=0 
--------------------------------------------------------------------------------------
Google spreadsheet does not support the SUMIFS function (as of Jun-2012 anyway) ... however, I have use the SUMIF function in the following solution to the problem

yogi_Display Commission Earned By Viewing Team Member Only And Not Other Team Members

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #597   Jun 23, 2012     www.energyefficientbuild.com.

user senuuar said:
Interesting spreadsheet question
Good Afternoon,
I have an interesting issue I would like your guys' opinion on.  I have a spreadsheet that tracks employees commissions.  I wanted to have a master spreadsheet so I originally created one spreadsheet that, in column A, listed the employee's name and then in columns B onward it listed their position, commissions earned, notes, etc.  Recently, my employees have wanted to be able to see their commissions as the month goes on.  However, I do not want to open them up to see everyone's commissions in the company as that is private information.  My first thought was to create individual spreadsheets, but I have to be able to have a master spreadsheet as well.  Then, I intended on using =importrange to pull the data from my master spreadsheet into the corresponding spreadsheets, filtering based on the employee's name, and then locking the spreadsheet so they could only see their own information and it would update automatically as new entries were made on the master spreadsheet.  Unfortunately, google restricts you to only using 50 importrange functions in a single spreadsheet so this will not work in my situation.
Is there any way you guys can think of to filter these results so each employee can view only their information?
Thank you for your help!
---------------------------------------------------------------------------------------
following is a solution to the problem
first let me show an image of the Commission Data spreadsheet

Please note that I add a Team Member Commission Table in the Commission Data spreadsheet. In this illustration, I have also added the Passcode of Team Member in the Commission Table ... but one can do without having to add the Pass code for each transaction by the Team Member.

so without much further ado, here we go ...

yogi_Compute Prices Based On Input Parameters In Different Sheets

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #596   Jun 17, 2012     www.energyefficientbuild.com.

user datonn said:
IF formulas with multiple parameters (greater than and less than)
Hello everyone!

Say, I have a question that I have not been able to figure out, related to "if/then" types of output and multiple qualifying criteria/parameters inside Google Docs Spreadsheets.  I have a spreadsheet formula that I am trying to create which would essentially do the following:

I want to have users of a spreadsheet input three simple variables:

A. number of panels on a print product (either 6, 8 or 10)

B. size of the output (Small, Medium, or Large)

C. number of printed units to order...using something like the below:

If units is a value of between 0-500, then return a value of ("Minimum of 500 required"))

If units is a value of between 501-1000, then return a value of (B3*(3.33*1.1)) 

If units is a value of between 1001-2500, then return a value of (B3*(1.89*1.1)) 

Basically turning that "matrix" into one bottom-line number which I can then provide to individuals saying what said options would ultimately end up costing them.  A and B putting users into a category/basket of pricing information, then C basically multiplying the number of units by a different range of prices (get steadily cheaper, the more units one wishes to order).

I'm good at formulas, but something this complicated is giving me fits!  Consequently, if anyone has any ideas and/or can point me to tutorials or other discussion group posts which might lead me to a solution, I would greatly appreciate it.  FYI, I spent about an hour this morning searching through various posts and forums, and I am coming up blank.  Thanks in advance for any help I might receive!  - Derek

----

 I've since added new sheets that have that additional pricing information inside the same Google spreadsheet...as well as made the sheet editable to anyone with the link: https://docs.google.com/spreadsheet/ccc?key=0AkPHCtec6Y6idHdPRTNUbmEyMC1zeW8ydE5iOGxNTkE

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

following is a solution to the problem

yogi_Rank By Category With Condition

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #595   Jun 16, 2012     www.energyefficientbuild.com.


user bryan-p said:
Rank by category with condition
Need a formula to list the top 5 players by weight and lift, if reps = 1. 

Only the highest weight by a player can be shown, so the same player can't be listed twice.

Spreadsheet has data sheet coming from a Form. 

Assuming I need an ArrayFormula() with Filter() and Sort() in C5 and C11 in Leaderboard sheet, which has the expected result.

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

following is a solution to the problem

 

yogi_Compute The Commission For Sale Amount From Table Containing A Column Of Lower And Upper Amounts Concatenated

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #594   Jun 15, 2012     www.energyefficientbuild.com.

user AusDigitalMedia said:

Hi all. Can anyone help me with creating this formula for the D22 Cell in the attached file?
The desired outcome should be pretty easy to understand.

Attachments (1)

Digital Signage Project(1).xls

41 KB   View   Download

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

following is a solution to the problem

yogi_Extract Specified Set Of Columns From A Range That Contain Specified Strings In Any Of The Columns

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #593   Jun 15, 2012     www.energyefficientbuild.com.

user Dom_201 said:
Hello, 

I have been trying in vain to come up with a way to use FILTER to find and return questions and responses in a spreadsheet based on whether or not the "Tags" that were placed in the same row matched a specific set of "Tags" on the spreadsheet where the searching was being done from.  The goal was to make it easier to find questions that had specific tags so that we could use the answers that had already been written when responding to those questions via Twitter instead of having to re-write them every time.  I was able to make this work very easily when folks were only asking me to filter based on matching a single tag.  But recently I have been asked to make this work so that they could narrow down the list of returns by using multiple tags.  So, instead of returning results that matched one tag, now they want to return results that match multiple tags.  I tried using FILTER to do this, but I couldn't figure it out.  I also tried using QUERY, but couldn't get it that way either.  I will share my spreadsheet so you can see what I tried, but try not to laugh too much at my attempts to make it work.  :-/  I don't really know what I'm doing, I just keep trying different things until something finally works out...  

Ok, so, here's a link to the spreadsheet:

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

I left it the way that it is, not trying to cover up any of my blunders in trying to make it work.  :-/

If you look at the "Search" tab, you'll see that it worked just fine when I was only trying to filter based on one tag, but if you look on "Search 2" you'll see that all attempts that I made ended up failing...

Now, there are a lot of tabs in there, but you'll also see one called "Master" where I was basically going to just combine all of the Q&A tabs into one all encompassing tab and then just differentiate between the questions based on tags instead of creating multiple tabs.

So, the goal here is to be able to select one tag on the search 2 page and get a set of results, and then pick another tag on the search 2 page and narrow down those results, and another and another until I find the one I am looking for, or it no longer returns results because nothing matches that many tags.

I would truly appreciate any help you can offer.  I have used these forums in the past and I know that some of you guys REALLY know your stuff!!!  ;-)  I have seen you help out so many folks, so if you could possibly help me too, I would be VERY grateful...  Please?  ;-)

Thanks a lot for your time,

Very respectfully,

Dominique

P.S. I just remembered to add:  I am using Google Spreadsheets on an HP Windows 7 laptop using Chrome browser.

-----

Ok, I have put some sample data at the top of the "Master" tab for the purposes of clarifying what I am referring to....

I also made a few updates and cleared out all my old attempts since none of them worked anyways.  I still have all of that stuff if you need it, but it was cluttering things up.

Now, the only page that I am really concerned about is "Search 2".  The page "Search" was the first attempt and worked fine for what I was asked to deliver at the time.  But, since more is being requested than "Search" could perform, I created "Search 2" and "Master".  Thus, again, all I am worried about is "Search 2".

Speaking of Search 2, I added a column for "Link" because I want to return the Question, Answer, and Link for every applicable search.

Now, getting into expected results, I am going to put a few case scenarios and their explanations on the spreadsheet since that's the best way that I can think of to describe AND show you what I was hoping for.  I will put CASE 1, CASE 2, CASE 3, etc in column A of Search 2 and refer to those cases here.

CASE 1 - Tags = "Parent" (Cell: A3)

In this case, the formula used would return the following questions (along with corresponding answers and links)

Am I eligible for other education benefits?

Where can I find more information about aid?

Who is involved in the college and financial aid process?

What is the Expected Family Contribution?

What is the FAFSA for?

What documents do I need to fill out a FAFSA?

It returns these questions because somewhere between column D and column T on the "Master" page, there was the tag "Parent".  In this case, the instances of the tag "Parent" were typically in column F and G.  However, I would like to be able to return questions / answers / links with the tag located anywhere between columns D and T.  So, regardless of where that "Parent" tag is located between column D and T, if it matches the search, then the corresponding question / answer / link should be returned to the Search 2 page.

CASE 2 - Tags = "Parent" (Cell: A3) "Apply" (Cell: B3)

In this case, the formula used would return the following questions (along with corresponding answers and links)

Where can I find more information about aid?

What is the Expected Family Contribution?

What is the FAFSA for?

What documents do I need to fill out a FAFSA?

In this case, there are fewer results because fewer questions had the tags necessary to meet the search parameters (as I am calling them).  So, logically speaking, by setting A3 to equal "Parent" and B3 to equal "Apply" I am telling the formula to return to me all of the questions with corresponding answers and links that have both the tags "Parent" AND "Apply".  

In each CASE, as a new search term is added, fewer results would be returned and the list would become smaller.

CASE 3 - Tags = "Parent" (Cell: A3) "Apply" (Cell: B3) "taxes" (Cell: C3)

In this case, the formula used would return the following question (along with corresponding answer and link)

What documents do I need to fill out a FAFSA?

Because this question was the only one to meet all the search parameters, it is the only one that is returned.  So, logically, by setting A3 to "Parent", B3 to "Apply", and C3 to "taxes" I am telling the formula to return to me all of the questions with corresponding answers and links that have the tags "Parent" AND "Apply" AND "taxes".

CASE 4 - Tags = "Parent" (Cell: A3) "Apply" (Cell: B3) "taxes" (Cell: C3) "Receive" (Cell: D3)

In this case, nothing is returned.  There are no questions that have all four of the chosen tags, so no return is required.

Basically, this whole thing is to be set up as a way to narrow down the list easily to only questions that pertain to a specific set of topics.  So, if we want to respond on Twitter to a parent who doesn't know much about the FAFSA application process, and has asked us about what tax documents are required to fill out the form properly, we can narrow down a very large list of frequently asked questions and previously compiled responses to just those responses that pertain to the question being asked.

I really hope this explains what I am trying to do.  Basically, if a question has the tags in cells A3 AND B3 AND C3 return question/response/link for each question that meets the parameters...

Thank you SOOOOOO much!!!

Again, sorry for the delay.  Please, let me know if there is anything I was unclear about.  

Very respectfully,

Dominique Ramirez

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

in the following solution I created 4 separate sheets for each of the search cases

yogi_Present The Entity List Consisting Of Names And Frequencies Sorted By Frequency In Descending Order

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #592   Jun 15, 2012     www.energyefficientbuild.com.

the solution to the problem I have posted herein came about from a comment use G-squared had posted in my following blog post:
yogi_Generate A List Of Top Combination Of Locations And People In Descending Order,
http://yogi--anand-consulting.blogspot.com/2012/06/yogigenerate-list-of-top-combination-of.html
instead of posting a solution that blog post, I thought it would be better to post a solution to a generalized problem in a separate post ... so here we go:

yogi_Generate A List Of Top Combination Of Locations And People In Descending Order

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #591   Jun 14, 2012     www.energyefficientbuild.com.

user kevinhtre said:
I have a set of data that lists dates, people, and locations (of which there are a finite number). I am attempting to produce from that a list of the "top X" for each location. So for instance:

1/17/12     John     New York

2/13/12     Bob      New York

3/1/12       John     Dallas

5/1/12       John     New York

6/1/12       Bob      Dallas

I am trying to produce from that:

New York: John, Bob   [ed. note: John first cuz he appeared twice above]

Dallas: Bob, John [or whatever, they both appeared once so it's a tie]

etc

I'm having a devilish time.

Thanks for any help.

----

I'm able to use a FREQUENCY to create a two-column list of pairs like so:

=ARRAYFORMULA(FREQUENCY(MATCH(A1:A14,UNIQUE(A1:A14),0), MATCH(UNIQUE(A1:A14),UNIQUE(A1:A14),0))) 

but it really leaves me with individual columns, and not pairs, which is difficult.

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

following is a solution to the problem

yogi_Set Up In Column D 50 URLs Based On Sheet Numbers In Column B And Spreadsheet Keys In Column C

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #590   Jun 11, 2012     www.energyefficientbuild.com.
user tabus said:
multiple if-clause problem
Hi everyone,

The problem I encountered seems ridiculously easy, but after tens of tries, I still can't figure out how to fix it:(

Here we go:

col.A - empty

col.B - consecutive numbers: 1 onwards, achieved through "arrayformula"; ocassionally it gets text value instead of number

col.C - depending on neighbouring cell's value in col.B

           if 0<value<51 then "text 1"

           if 50<value<101 then "text 2"

           if 100<value<151 then "text 3"

           and so on...

So, the table should look like:

col.B       col.C

   1             text 1

   2             text 1

   ...            text 1

   50            text 1

   51            text 2

   52            text 2

   ...             text 2

   100           text 2

   101           text 3

Looks really simple, right?

So I use this formula in col.C:

=arrayformula(if(ISNUMBER(B3:B)=true;if(and(B3:B>0;B3:B<51);"text 1";if(and(B3:B>50;B3:B<101);"text 2";if(and(B3:B>100;B3:B<151);"text 3";"here another if-clause")));"n/a")) 

The issue:

As long as the value in col.B is between 0 and 50 (incl.), the formula works OK, but as soon as it turns into 51, I don't get any the "text 1, 2 or 3" but "here another if-clause" instead.

What's wrong??

It seems as if "B3:B" is read: "check the last value in col.B"

How do I fix it?

Any help appreciated!

----

Thanks for your quick reply,

Although the solution you've come up with does seem to work, it is unfortunately not really what I was trying to achieve:(

Well, probably my description wasn't clear enough, but for the sake of clarity I simplified the formula presented in the 1st post

So, let me now explain the problem in detail.

col.B

-it is filled up automatically with numbers from 1 to ..., they increase gradually by 1

-the values in col.B actually stand for "gid" number, used later on for producing specific urls

col.C

-here I take the corresponding value from col.B and produce an url, according to pattern: "https://docs.google.com/spreadsheet/pub?key="&"...key value..."&"&single=true&gid="&B3:B&"&output=html&gridlines=false"

-due to "importrange" restrictions, specific "...key value..." can only be used 50 times in 50 separate urls

-so my idea was to change "...key value..." (i.e. use another doc) when gid number turns from 50 to 51, then 100/151, and so on

that's why I wanted to make separate "key values" dependent on the value from col.B (i.e. gid number)

and again, the formula I presented above works until the value in col.B is <51 - a nice url is produced, but since 51 it only shows the option "here another if-clause"

I can't get it work on given if-clauses

If it's clearer now, can help me out with that?

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

based on my understanding of what the OP is asking for, let us have a look at the following solution

 

yogi_Extract Totals Of A Range Of Columns And Present Results By Year And Month Based On Dates In Column 1

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #589    Jun 11, 2012     www.energyefficientbuild.com.

This blog posts is for extending the solution where I had computed and posted the totals by Month in my following blog post:
http://yogi--anand-consulting.blogspot.com/2012/06/yogiextract-totals-of-range-of-columns.html
In this solution, I compute and post the totals by Year and Month.

yogi_Extract Totals Of A Range Of Columns And Present Results By Month Based On Dates In Column 1

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #588    Jun 11, 2012     www.energyefficientbuild.com.

user Nieboy1 said:
Column filter and return number of rows with requested filter attributes
I am Trying to filter a column and return a the number of rows that fit the filter query.  I am unfortunately not too familiar with anything but the basic use of equations in spreadsheets so any help would be greatly appreciated.
-----
 here is the spreadsheet I am referring to https://docs.google.com/spreadsheet/ccc?key=0AuXtZZ2omsU_dFJhVFdfM0dSR2ZfTklKakFnVW5WSVE im trying to have the form submissions get tallied up according to there corresponding month like I have done in the derivatives sheet under Contracts. 
------------------------------------------------------------------------------------------------------
following is a solution to the problem

yogi_Setup Individual Sheets Named After The Persons Where Only Data For Sheet Named After The Person Is Pulled From Sheet1

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #587    Jun 10, 2012     www.energyefficientbuild.com.

user Gaming said:

Here's a spreadsheet demo

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

Now pay attention.

Tim, Paul and Jon are now going to go over the results. They find their name in the filter and see the results they have to go over. Now when they are doing this all at the same time, the filter always switches. So if Tim is going over the applications, and then Jon comes and selects his name for the results, it changes and Tim has to then change back to show only his name, but then Jim has to do it also, so it's kinda obvious that this isn't very helpful to have it on 1 sheet. However, I would like to have the results on multiple sheets, so they can all have specific filters for every sheets. I'm asking if there is a possibility too show live results on every sheet.

Thanks

------------------------------------------------------------------
in the following solution to the problem ... I have created a separate sheet for each of Tim, Jon, and Paul ... well without much further ado, let us have the solution do the talking