yogi_Sum Up Values by Key That Match The Items In A List

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user bvboe said:
I have three columns, a key and value pair, and a list of keys. I want sum the values for all keys that appear in my list of keys. Eg:
KEY VALUE LIST
A     1    A
A     2    B
B     3    B
B     4    B
C     5    A
C     6    Z
D     7
D     8
What I am trying to get is
RESULT
3
7
I have searched and tested for months with no success. Any help would be enormously appreciated.
2011-12-29
Sorry, I just realized I wrote the subject wrong, it should b:
select sum(b) where a IS IN ARRAY(c2:c)
--------------------------------
following is a solution to the problem

yogi_Get Map And Compute Distance Between Start Address And Destination Address

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

some formulas we had developed earlier to compute distance between two addresses as reported in some recent posts in Google Docs Help forum stopped working a few weeks ago.
So here I present a solution to the problem -- and the new formulation is a lot simpler than in the older formulas that we had presented in some earlier threads in Google Docs Help forum:
------------------------------------
so here we go

View Larger Map

yogi_Pull Data From Another Sheet From Variable Fields And Rearrange Pulled Data A Specified

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

user Grimatoma said:
I am trying to make a filter that is dynamic.
i have 3 columns B,C,D and i want a filter where if i type in the column name in lets say cell F1 column G will then replicate the data from same title from one of the columns B,C or D, and the titles for B,C,D are on row one
Ive been trying to think of different ways on how to do this but i keep getting stuck with the issue of not being able to convert the column locations into a range.
Thank you for helping!
https://docs.google.com/spreadsheet/ccc?key=0ArCZe6EHA8B8dFpfUmJVanp0YVZtX2NWelE1ajBGS2c
the dummy sheet is public for anyone to edit it for now
----
ok, I run a paintball team at my school, and i run the entire team out of google spreadsheets. currently what have a sheet for the coaches to put players on individual lines for different events, rows B,C,D are the peoples lines. what i want to do is on another sheet or for right now, it can be right next to it. i want to display some information about the event. pretty much each line split up with the names in the correct spot. but that is the easy part.
the problem that i am having is from having a cell where i can write the name of the event to then have a filter or query then can grab that name and look at the columns titles get the right one and list all of the data for me to then modify.
for the Query function ya i am okay with using this.
-------------------------------------------
then finally the OP shared another spreadsheet with more specific question(s)for which I post a solution to the problem

yogi_Detrmine Whether List Of Needed Items In An Array Match The Items In A String

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

Docs_user12345 said:
I need a help with a formula, I tried but I'm on a dead end...
Example spreadsheet
The example sheet is a simplification of what I need, it's probably not clear what's it for but it's too long to explain...sorry!
Cells B4:B12 is what is available and cells C4:C12 is what's required, if there is a mismatch it should return "No" in cell E4.
Both cells B4:B12 and C4:C12 can change (see B16:B24 for all possibilities).
I don't doubt that there is a formula for this but I can't figure it out.
-----------------------------------
following is a solution to the problem

yogi_Apply Formulas To FormSubmittals Involving Previous Row Values

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user VTXMan said:
Apply formulas to form-inserted rows
I am trying to make a form/spreadsheet to track my fuel mileage. I have the form built and it populates the data nominally. However, the formulas for calculations I perform on each entry do not get added to the new rows.
I have searched and found about 100 threads all saying to use ArrayFormula(). So, I have changed my formula from =(E3-E2) that calculates the miles driven, to =ArrayFormula(IFERROR((E3-E2))).
This formula properly calculates the field on the rows already in the sheet, but when the form adds a row, the formula doesn't copy down the column to become =ArrayFormula(IFERROR((E4-E3))). The new field is just blank.
What am I missing?
-------------------------------
following is a solution to the problem

here is the Form that I used to populate the Form Responses sheet

and here is the Summary of response

yogi_Handle Variable Sheet Names With Indirect Function And Array Formula Across Multiple Columns And Rows

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

user krazyderek said:
i have a workbook with sheets named 10, 20, 30 etc... On the Totals sheet i count the occurance of a number on these sheets, each row shows the count of that number on that sheet.
I have a formula that i can drag to populate the matrix,
=if($A2>0,count(iferror(filter(indirect("'"&B$1&"'!$A$2:$A"),indirect("'"&B$1&"'!$A$2:$A")=$A2))),"")
but the real application is quite large, and i'd like to just control it with an array formula so i can update, or delete it without having to drag it out to the rest of the column x row range
i tried =arrayformula(if($A2:$A6>0,count(iferror(filter(indirect("'"&B$1:D$1&"'!$A$2:$A"),indirect("'"&B$1:D$1&"'!$A$2:$A")=$A2:$A6))),""))
but it only continues down the the first column, and the values are all the same as the source cell
here's the link to the spreadsheet sample https://docs.google.com/spreadsheet/ccc?key=0AtSGvdldrQk3dGYtRUZXVzV1dEZsWkFqV1pOZEZySWc
-------------------------------------
following is a solution to the problem

yogi_Average Cells From Differents Sheets With Value Greater Than Zero

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

user Squarky said:
I have spreadsheet that calculates event scores for multiple meets. I want to average these scores on a separate sheet. I know how to average all of the events, but how do you only average the events that have been completed. Meaning I want to average cells across multiple sheets that have a value greater than 0.
Current Equation:
=AVERAGE('Pineapple Classic'!E2,'Windy City'!E2,'Swiss Turner'!E2,'Iowa Boys Invite'!E2,'Great Western Invite'!E2,'Joe Giallombardo'!E2,'State Championships'!E2,Regional!E2)
-----
BTW I did try this formula without any success:
=AVERAGE(IF(N(INDIRECT("'"&{"Pineapple Classic";"Windy City";"Swiss Turner";"Iowa Boys Invite";"Great Western Invite";"Joe Giallombardo";"State Championships";"Regional"}&"'!E2"))>0,N(INDIRECT("'"&{"Pineapple Classic";"Windy City";"Swiss Turner";"Iowa Boys Invite";"Great Western Invite";"Joe Giallombardo";"State Championships";"Regional"}&"'!E2"))))
----------------------------------------
following is a solution to the problem

yogi_Calculate Total Amount For Taxable and Non-Taxable Items Sold

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

user m-j-p said:
I am trying to calculate only particular cells in a range. For instance:
Column A | Column B
1 Yes |  $5
2 No  | $10
3 Yes | $20
4 No  | $15
5 No  | $20
I would like to perform the following calculation. If A1 = "Yes" then add 6% to B1 and apply that same rule for a range of cells to arrive at a total. So for instance the calculation in the scenario above would be: A1 * 1.06 * B1 + A3 * 1.06 * B3 = $26.50. I'm basically using this formula so I can quickly calculate all of the taxable sales for when I do my sales tax at the end of the month.
update:
Thanks again for the help!
One more question regarding this same formula. How would I properly write the formula to have another IF statement?
For instance: IF((E1;"Yes";F1)*1.06, IF(E1;"No",F1)*1) ? Basically going to have a subtotal and total column and the total column will add 6% to the subtotal if "Yes" and if "No" it will not add to the total.
-------------------------------
following is a solution to the problem

yogi_Compute Points Away From Reaching A Level From A Number Of Specified Levels

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user sarebear62 said:
a formula that will subtract a particular column's value from one of the next three columns' values . ..
whichever one it is less than, but if it's greater than the 1st of the three columns and less than the 2nd, it's subtracted from that one, and so on for the third column.
The three columns are three benchmarks towards a goal, and I want to subtract the main first number (the column just before the three benchmark columns) from the most appropriate benchmark, so I know how far I have to go to reach that next benchmark.
If done it both as an array and not, both with the and's and not, simplifying it down to just if it's less than the first benchmark, subtract it from that, otherwise if it's less than the second benchmark, subtract it from that, and so on for the third.
I've ended up with parsing errors, and wrong number of arguments to iterate . . . not sure what I'm doing wrong but I'm no expert!
=ArrayFormula(IF(O2:O12P2:P12, O2:O12Q2:Q12, O2:O12 There is how I have it, in one form, the O column being how much I've accrued towards my goal, columns p, q, and r being equivalent respectively to sort of a "bronze", "silver", and "gold" goal levels, lol. I've read for a couple hours and searched but from everything I've seen I'm doing it right? So what's wrong?
----------------------------------
following is a solution to the problem

yogi_Custom Format A Numeric Cell With Some Text And The Cell Should Remain As Numeric

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user Ewerton Emmanuel said:
Folks,
Do you know how can I put a number and a text together considering the cell as NUMBER? For example I have some cells with a number such as 100 and I need to include the respective unity in it "t/h", i.e: 159 "t/h", but, the cell must be a number. In Excel I simply custom the cell, but here I really don't know.
Can you help me?
Thanks.
----------------------------------
following is a solution to the problem

yogi_Extract Non-Duplicated Cells Meeting Specified Criterion

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

user trivox2 said:
reference spreadsheet:
https://docs.google.com/spreadsheet/ccc?key=0Aj7xwed9XeVNdFc3Mjc1UGdQZjBsVThjaWppaDZnaXc

I've got a google spreadsheet that is scraping multiple sites for races where I live.  I want to be able to sort out the duplicates in my final spreadsheet, but sometimes the sites list slightly different names for the races.  I was hoping someone might be able to help me create a function that will take the first 4 characters in each cell and compare them to adjacent cells (the data is sorted A-Z by race name so that should be fine) and then delete one of the rows if the first four characters match.  Any ideas? I was hoping there would be a way to define a range other than just cells using the UNIQUE function, but I don't think that's possible.

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

following is a solution to the problem 

yogi_Set Up To Automatically Sort Data By Priority And Time Taken Columns In Another Sheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user praber31 said:
I have a spreadsheet that has 5 columns. Task, Next Step, Time Guess, Time Taken, Priority.

Time Guess is in minutes and Priority is A,B,C,& W. 

I would to have it auto update my form based on Priority (A-Z) and then Time Guess (A-Z). This way the top of the list would always have the highest priority that can be completed in what I think is the shortest amount of time. This spreadsheet would then auto sort when I change the next step's time or priority.

Any help would be appreciated. 

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

following is a solution to the problem:

yogi_Tabulate Sum Of Accounts For Account Names Delineated In A String

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user gary.jenkins said:
I am new to this stuff, but having fun. I am not sure I understand if thereis a solution to this. Attached is a simplified Spreadsheet. Columns A, B, and C contain data. Cell F2 contains the sum of column A, G2 = sum of column B, and H2 = sum of column C. Is there a way to do this where the formula is defined in F2 only ... and extended to G2 and H2?  
....
 I can do that ... and have. The problem is that the spreadsheet I am working on is quite a bit more complex, and I would like to be able to do this without copy/paste. For instance, I can do it like this: F2  ={sum(A2:A),sum(B2:B),sum(C2:C)}

Still, I would like to be able to simplify one more step: For X=A,B,C ... F2 = sum(X2:X)

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

let us see if the following solution to the problem would do for you:

yogi_Count Total and Attributes By Category For A Specified Entity

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user Drop Knowledge said:
Here is a small sample of the data I will be using:
https://docs.google.com/spreadsheet/ccc?key=0Ai0NLpQcw2ccdC01VTRvdmZFQVVPQ1llOXNtbjJKLVE
Basically, I want to be able to count how many times each type of issue is reported per agent.  Using the above example, I'd want to know how many hardware issues, software issues, etc. were assigned to Terrel in the appropriate cells under Agent Breakdown > Terrel.
Note: I just manually entered the total tickets in the example as I already have a formula to count total tickets per agent.  Any help would be appreciated!
--------------------------------------------------
following is a solution to the problem

yogi_Calculate Running Balance For Multiple Investment Line Items

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user IamPalmer said:
I am trying to calculate a running balance on a multiple investment account.
I make an investment (Column A)
I either get a positive return, or a loss of the initial investment (Column B).
I want to be able to adjust my balance by deducting the loss (Column B negative number), or adding the positive return (Column B positive number) PLUS my initial investment (Column C) to the balance (Column D).
I am currently attempting to do this with multiple if/then's, but I have been unable to find a similar formula and I am too much of a newbie to figure this out.
I appreciate any help that anyone can provide!
----------------------------------
following is a solution to the problem

yogi_Count Number Of Specified Entries In Specified Cells Row By Row

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

user lavaman101 said:
So ive got a spreadsheet where the possible answers are either an "L", "A", or "W"... i want to be able to create a sum of each type of letter in each row... so a sum of all the "L"'s in one cell... and a sum of "A" in a different cell, etc. 

I was thinking an IF statement would work... and it does for 1 cell... but not for an array of cells.  I tried =IF((C4:H4="L",1,0))

Any help would be great! 

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

following is a solution to the problem

yogi_Count Unique Values With Multiple Conditions

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

user lambchop said:
Hi, I am new to Google Docs and I'm trying to count unique entries for multiple conditions.  A simple example I have created is to count how many cities each employee is booked to visit and how many cities they actually attend.  Each city may only be counted once for each employee.  See example below,

EMPLOYEE  CITY         BOOKED VISITED

JAMES       SYDNEY           Y Y

JAMES       PERTH            Y N

JAMES       SYDNEY           Y Y

JAMES       BRISBANE         Y Y

SALLY       PERTH            Y Y

SALLY       PERTH            Y N

SALLY       DARWIN           Y Y

ROGER       CANBERRA         Y Y

ROGER       ADELAIDE         Y N

In this case, the results would be

EMPLOYEE BOOKED  ATTENDED

JAMES      3        2

SALLY      2        2

ROGER      2        1

I've created a test spreadsheet at the following link.  The results table above is located in sheet 2 named "RESULTS"

https://docs.google.com/spreadsheet/ccc?key=0AsiZG36EVbnAdHVuN1N6VTl4aXg5Qm81Z29zOTh2bHc&hl=en_GB#gid=0

I hope this is the right way to share this file.

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

following is a solution to the problem

yogi_Compute Rental Price Based On Specified Date By Period

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user dragosxps said
How can i calculate price based on specified dates

Thank you for reading my post ( i searched before but i haven't been able to find a solution )

in Sheet1 I defined 5 periods and the price for each period + day price

in Sheet2 I defined the period for which i need a price

the price formula should do something like this

1. Identify in which period the specified range is located

2. calculate sum for total period

so in the example of spreadsheet for 01 jan - 23 jan  i have

01 jan - 05 jan - 4 days = 4*25  = 100 +

06 jan - 10 jan = 200+

11 jan - 20 jan = 300 +

21 jan - 23 jan = 2*100 = 200

Total should be = 800

The spreadsheet is public here:

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

Thanks once again for your time

------------------------------
the computed price of $800 as shown by dragosxps is based on 19 days rental although there are 22 days in rental period ... so in my opinion $800 is in correct. I am going to base it on 22 days rental. Also I am going to assume that rent for 5-Jan to 6-Jan is $50; rent for 10-Jan to 11-Jan is $33.33; and rent for 20-Jan to 21-Jan is $100 (based on prorating per dragosxps' table)