yogi_Compute Sums Of Numbers In A Column Meeting Multiple Criteria

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #670   Aug 04, 2012     www.energyefficientbuild.com.


user Wenchie said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/6PI1VFTKxn4)
write formula sum of column that meets multiple criteria
I have a document that lists items and their cost as well as whether they have certain functions.

It is currently set so that Column A lists the items by name, Column B the respective prices, and then there are two extra features of these items listed for column C & D with "Y" or "N" to indicate whether this item contains the respective additional features.

I was hoping to be able to have a formula to cover the sum of the values of Column B for each combination of the two additional features...so for example a sum total of column B for all entries that have a "Y" in columns C & D, one for sum total of Column B for entries where C & D are both "N", and one each for the sum total of B if C="Y" and D="N" and vice versa.

So far the closest I can get is by running a range function which will encompass the result of one column: =SUMIF(C:C,"Y",B:B)

Could anyone help me with how to expand this to encompass both variables?

Thank you in advance for any help. It is greatly appreciated by this lowly noob.

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

following is a solution to the problem

yogi_Rearrange Data By Entities In Column B C D And Set Of Specified Number of Columns _Solution2

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #669   Aug 04, 2012     www.energyefficientbuild.com.


user aleksialkio said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/n4xP-jmDtXQ
Using query to reorganize the player details
Hi,

I'm trying to organize the spreadsheet to a new way. I'm wondering is the query function able to do this and I have tried to think it with many ways but I do need some help. Perhaps I'm thinking too difficult :-)

I have a form that collect the information from one team to one row. I need better way to show them and I will make individual sheets for every age group to publish. I made an example sheet what I need to do.

https://docs.google.com/spreadsheet/pub?key=0AkMVQyM7UcFddGg0SG56LTFlRVNZZTVIZGNzUHFJLVE&output=html

Thanks, Aleksi

----

I had provided a solution to Aleksi as presented in my following blog post:

yogi_Rearrange Data By Entities In Column B C D And Set Of Specified Number of Columns

http://yogi--anand-consulting.blogspot.com/2012/07/yogirearrange-data-by-entities-in.html 

----

however Aleksi commented

Hi Yogi,

Thanks for the example. I tried it and it works. Then I realized a huge problem: I will have 800 teams in one season and 80 colums in the main page (form) so it means 64.000 cells and because I have 6 individual sheets for every age group, it will double 128.000. This is still under the limit so everything is okey.. If I use Index formula as in your example, I will have approx. 800 teams * 15 players * 5 cell = 60.000 formulas and that's over the limit. Query or arrayformula would fix this problem because the continue is not caltulated as a formula (as I red from another post). Now the problem is how to use this formula in my case?

Thanks,

Alexi

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

I don't know about Aleksi's project scope, constraints, and preferences, however, I have presented another solution in my following blog post

wherein I have taken the source data in Sheet1 and prepped it foe QUERYing (see yogi_Sheet1) and then I have extracted data by Club, Coach, and Manager

yogi_Extract Specified Number Of Top Scorers From A Range Of Names Ans Scores In A Sheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #668   Aug 04, 2012     www.energyefficientbuild.com.


user pbear2101 said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/YnNFTLadEc4)
Top 5 Records In a Sheet
Hello,

I have a spreadsheet That I will be constantly updating with new info. 

There are two columns of info, one a column of names, the other a column of numbers.

What I want to do is list the top five Number/Name combinations on another sheet in the workbook.

New Name/Number combos will be added to the list as time goes on.

Another Difficulty is that the Number Column is updated by a =Countif function and wont be updated manually, And is in no way tied to the Name Column.

Is there a formula or workaround I could use to Transpose the Names and Numbers ONLY IF they are the top five in an array?

----

This is for an online game, I'm tracking the number of actions taken by certain players by taking raw data and organizing it into different groupings, I have that aspect take care of.

Now I have to take each grouping (sheet) and make it so the people with the Amounts are listed in order on a seperate sheet

The picture I've attached is a basic idea of what I am looking for. Except my spreadsheet is much more elaborate since all the data is compiled via formulas and raw data from a master list.

I need the list on the right to update automatically from the list on the left. The list on the right needs to have the current top 5, which could change from minute to minute

Attachments (1)

top5.jpg

95 KB   View   Download

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

following is a solution to the problem

yogi_Set Up A Time Sheet With Hours Worked Rounded As Specified

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #667   Aug 03, 2012     www.energyefficientbuild.com.


user HankAtRMGsaid: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/F6cYAxRASPM)
Timesheet rounding issue
I have created a time sheet with Time-in, time-out and a totaling formula that is supposed to round to the nearest 1/4 hour. I grabbed the formula from a website, I forgot where.

I have created a public copy here: http://bit.ly/RifSLA

It works pretty well, but the rounding in "Hours" column is not perfect, as you can see. It rounds up or down by .5 when I enter a "Begin time" or "End time" that is on the 1/4 hour, e.g., 10:15, 9:45., and the other entry is on the hour or 1/2 hour.

Could someone look at the formula and help me correct that? Thanks.

- Henry

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

following is a solution to the problem

yogi_Sort In Ascending Order A Sheet By #n Which is Part of Cells In Column B

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #666   Aug 03, 2012     www.energyefficientbuild.com.


user Sander121 said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/SjU-fCoQZ2Y)
Hello,

I want to sort my sheet, but not from A > Z. It should be that when i arrange the column, the other cells in that row should go with it. Does anyone know how to do that?

Example:

https://docs.google.com/spreadsheet/ccc?key=0Aq9qjbt_5Ja_dFp0cUxRcFF1QjYzVlZKdmpuS1Jjb2c#gid=3

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

following is a solution to the problem

yogi_Generate Unique Identifier With Specific Format In Sheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #665   Aug 01, 2012     www.energyefficientbuild.com.


user Vinnie 2 said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/c-bgUwMSfac)
How to auto generate a unique identifier number with specific format in spreadsheets ?
 I was trying to auto generate a serial number list starting with '0001/12' format, where 0001 stands for serial number and 12 stands for year 2012. I tried with function =0001/12 in A1, the result was a divided number. How could I generate a serial number with above format without division. 
------------------------------------------------------------------------------------------
following is a solution to a bit more generalized problem