yogi_Compute Row By Row Count Of Specified Strings In Multiple Ranges Spread Over Several Sheets

                                          Google Spreadsheet   Post  #1165
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 01, 2013
user dlgarbett  (http://productforums.google.com/forum/?zx=crpyw0e3sdva#!category-topic/docs/spreadsheets/GNxGPYa_6Vs)
countifs in google spreadsheets
I want to count the number of times a particular string appears in specific rows on my spreadsheets.

https://docs.google.com/spreadsheet/ccc?key=0Ar4sAfkuueMJdFMyNHBiZHVOVFZqVTNyM2c2eF9QUlE#gid=4

see sheet "totals" B119 my formula if it were in excel would be

=countifs(April!C18:G18,April!C37:G37,April!L18:P18,April!L37:P37,May!C18:G18,May!C37:G37,May!C56:G56,May!L18:P18,May!L37:P37,June!C18:G18,June!C37:G37,June!L18:P18,"*SD*")

I have used countif(range,"string")+countif(range,"string")................before for this and I can use it here as well, but I think there must be an easier way.

Please help!

Don

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

As of now (May-01-2013) Google spreadsheet does not support the use of COUNTIFS functions ... however have a look at the following solution to the problem

yogi_From A Column Of Entries Pull The Exact String As Specified

                                          Google Spreadsheet   Post  #1164
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 01, 2013
user vif1000  (http://productforums.google.com/forum/?zx=crpyw0e3sdva#!category-topic/docs/spreadsheets/3cjBewdi2SY)
=arrayformula( if( iferror(search($B9:$B22;J5)); $B9:$B22; iferror(1/0) ))
Trouble with formula from H.:  =arrayformula( if( iferror(search($B9:$B22;J5)); $B9:$B22; iferror(1/0) ))

Please look into this sheet: https://docs.google.com/spreadsheet/ccc?key=0Ai29kwV35K0SdDAtRVRWNUg2VWw5Q0lpSVJUU0ZzeGc&usp=sharing

Sheet 7.

The result of formula can only give on uniqe result, but now it gives 2.,,.

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

following is a solution to the problem

yogi_Value In Column B For Latest Date In Column A For Specified Month -- WorkAround For LookUp Function In Excel

                                          Google Spreadsheet   Post  #1163
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 29, 2013
user Malbec159  (http://productforums.google.com/forum/?zx=crpyw0e3sdva#!category-topic/docs/spreadsheets/aNrTUWn8vgI)
IFERROR formula imported from excel does not work in Gdocs
I have a formula that works fine in Excel, but when I import that excel spreadsheet to Gdocs, the formula doesn't work. I have attached the original excel spreadsheet, and the below is the gdocs spreadsheet link. Any help to make this work in GDocs would be appreciated. :) 

https://docs.google.com/spreadsheet/ccc?key=0ArpmMlaQcWc6dDctNllQX2hibmVmR1g1d2F5UHpzM0E&usp=sharing

This is the formula that works in excel, but not in Gdocs:

=IFERROR(LOOKUP(2,1/((MONTH($A$1:$A$366)=MONTH("1 "&D1))*($B$1:$B$366<>"")),$B$1:$B$366),"")

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

following is a solution to the problem

yogi_MultiCondition Count Of Unique Entities In Column A Provided There Is No Corresponding Nil In Column B

                                          Google Spreadsheet   Post  #1162
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 29, 2013
user Lewis TW Koh (https://productforums.google.com/forum/#!mydiscussions/docs/STG-8g-mJjw)

Hi Yogi,

Reference at https://docs.google.com/spreadsheet/ccc?key=0AjJXY6WtI3JmdHRNVUhmZU9EdDRmM0stUHVhUkNlUnc#gid=0

I have shortened the list to make it more clear.

There are 9 fruits (Apple 1,2,3, Pear 1,2,3, and Orange1,2,3)

Orange 1 and Pear 2 is marked "Nil" in column B.

Pear 3 is repeated in Cell A11 (should not be counted)

The total number of fruits in cell C16 should be 7 instead of 8. It is currently showing 8 because the formula counted Pear 3 in cell A11.

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

following is a solution to the problem

yogi_If Store Number Exists In Areas Sheet Column A Mark In Orders Sheet Yes Otherwise Mark No

                                          Google Spreadsheet   Post  #1161
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 29, 2013
user Charlene On Green (https://productforums.google.com/forum/#!mydiscussions/docs/4ABVuEHA8vI)

I have a sheet of stores sorted by store #, lets call this sheet1, I have another sheet that the reps fill out, lets call this sheet2. If a rep enters the store number on sheet2, I want to check if that store is in my territory on sheet1. If the store is in my territory I want to put "IN" on the row 3 below the store number that the rep put in.

 

What kind of array or if statement would I use for this?

Thanks!

---

Yogi, thanks for your response here is a sheet

 

https://docs.google.com/spreadsheet/ccc?key=0AsgsqMHzLkIWdENxRWpPbU5MRDlZRmVuSU5jNXRTUEE&usp=sharing

 

on the sheet called "orders" I want to know if value in ROW 2 matches up with value in column A on ʺareas sheetʺ then type in the word ʺYESʺ, otherwise type ʺNOʺ

 

Thanks!

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

following is a solution to the problem

yogi_Compute Count Of Win Loss Tie And Present Results In A Single String

                                          Google Spreadsheet   Post  #1160
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 28, 2013
user Joe Stennet : (https://productforums.google.com/forum/#!mydiscussions/docs/YytJcX_iPjs)
Countif?
Hi,

I am trying to edit a spreadsheet used by my daughter's softball league to count the number of wins/losses/ties in a range and report it (ex: 8/5/1).

I tried =COUNTIF(G4:G17, "Win")&"/"COUNTIF(G4:G17, "Loss")&"/"COUNTIF(G4:G17, "Tie"), but it doesn't work. What am I missing?

Thanks!

-Joe

---

Thank you... Hopefully I did it correctly...

https://docs.google.com/spreadsheet/ccc?key=0An0Y5ZY61YGJdFRleV9SUWV3VmNVOFpGcktJeFcxX1E&usp=sharing

Joe

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

here we go

yogi_Pull Data For Specified Property Location From A Table In Another Sheet

                                          Google Spreadsheet   Post  #1159
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 27, 2013
user Michael Alexander :

Yogi i have been following your posts on google spreadsheets and could really use your help get a sheet to work. I am trying to create a master data sheet for forms then have data based on the location field pull to a different tabbed sheet. 
-----------------------------------------------------------------------------------------------------------------------------
here is a solution based on my understanding of what Michael is trying to accomplish

yogi_LookUp Multiple Column Data For A Specified Entity From Another Sheet

                                          Google Spreadsheet   Post  #1159
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 27, 2013
user Xavier Marques :(https://productforums.google.com/forum/#!mydiscussions/docs/JwHwzXN4XNg)
How do I reference cell values between sheets?

Hi!

I have a sheet where every row contains a series of data relative to the same person.

(see attachment)

On sheet 2 I want to create a personal file containing all the data on the first sheet for a given person.

If I assign a value in a cell on Sheet2, for exemple B1 is 3 Is it possible to get these data using something like =Sheet1!B(Sheet2!A1) to get the name of the third person?

I want to make it so that if I want to get a personal file on any of the names, I just type their corresponding number and get sheet2 filled automatically.

Sorry if this is a bit confusing, but it's a bit hard to explain...

Attachments (2)

Screen shot 2013-04-27 at 10.49.55 PM.png

11 KB   View   Download

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

here we go

Screen shot 2013-04-28 at 12.20.55 AM.png

5 KB   View   Download

yogi_Sort AlphaNumeric Data Pulled From Column B Queried For Specified value From Column A

                                          Google Spreadsheet   Post  #1158
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 27, 2013
user Ben_______  :(http://productforums.google.com/forum/?zx=crpyw0e3sdva#!category-topic/docs/spreadsheets/TMhxH0b8JHo)
Return unique values in specific cell with no gaps - vlookup/unique functions? Please help!
For April's report I just want to pick out any values in column B that relate to that month and ignore the rest. How can I start in F2 and do this (top of the sheet)? I don't want any gaps in the list as this needs to be ready to download as a report - I am trying to save time you see! VLOOKUP can't quite do this as I am looking for only one value which is 'April' - examples provided.

I need to start in the top cell each time as this is where my report will start from, so I need to ignore any values from 'Dec' and 'May' yet still return any 'April' values in my first cell.

Added example here, any advice would be very gratefully received.

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

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

following is a solution to the problem

yogi_Compute Row By Row Average Of Scores By Two Sets Of Judges And Then The Final Score

                                          Google Spreadsheet   Post  #1156
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 26, 2013
user Lindy Bonin :(http://productforums.google.com/forum/?zx=crpyw0e3sdva#!category-topic/docs/spreadsheets/z2RhRe8Boi0)

Formula to get the MEAN of a set of data in same row, with a different divisor

Hi,

I'm in charge of Talent competition/event and have a lot of tabulating of scores to do. To save myself time, I've created a form that I will enter all of the judges scores in (as a grid, value 1-9). 

To get each participants final score, I have to get the average of each individual judge, and then get the average of all the judges scores together. All data is entered into 1 line on the spreadsheet from the form, making it hard for me to get an accurate average or mean of scores. NOT ALL CATEGORIES have the same amount of criteria the judges have to respond to. Example: Vocal solo has only 6 areas the judges will be scoring on, and piano has 7 areas, and choir has 8 areas they are being scored on. So naturally, the formula has to be divisible by the amount of criteria they are being judged on specifically, and not the amount of criteria for another category. 

I need to figure out/build a formula to use in order to keep each participants scoring in the same row... Is there an "IF THEN" statement? Anything?? 

I've attached two screen shots to help better explain my spreadsheet...hope this makes sense.  

ANYONE that can help ASAP would be great. This event is SATURDAY...it would be a huge time saver if I can pull this off! I will send whatever screen shots, more info if that will help. 

---

Hyde,

Thanks for your reply. Here is a link to a copy of the spreadsheet.

https://docs.google.com/spreadsheet/ccc?key=0AmOXGoHXyzkodGVNZlhBeUVDNWRUYlVqMUx0SXR5MEE&usp=sharing 

Thanks for any help you can offer! 

-Lindy 

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

following is a solution based on my understanding of the problem

 

yogi_Compute Sum Of Numbers In Column C For Numbers In Column B That Are Contained In String In A1

                                          Google Spreadsheet   Post  #1155
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 25, 2013
user Ludvig Hult :(http://productforums.google.com/forum/?zx=crpyw0e3sdva#!category-topic/docs/spreadsheets/27xD9fCuTE0)
Nifty way to do lookups and sum with input as a string?
Hi!

Im trying to create a smart formula for doing some lookpus. If i could do a FOR loop or such, i would have no problem, but im kind of new to thinking in spreadsheet formulas... 

My input is non-negative integers separated by commas, given in a cell. Say A1 holds that input. for example "200, 234, 1919, 31919".

In column  B and C i have key-value pairs. Keys are integers and values are floats.

Now i want to sum the values in column C corresponding to the keys in B that occurs in the input A1.

I tried this: 

=ArrayFormula(sum(not(iserr(find(B2:B,A1)))*C2:C))

it works quite nice, but if the key "200" occurs, and i have the input "2000", it will contribute to the sum because of the find-method behaviour. Is there any good workaround?

---

oh.

and here is an example sheet!

https://docs.google.com/spreadsheet/ccc?key=0AuGFBNbBip60dDZKWGQ4RmktLUJFZVgwY2lyUUxFLXc&usp=sharing

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

subsequent to a lively discussion in Google Docs User Forum in the following I present Hyde/Ludvig formula

which works for the case in the original post and some alternate solutions for a bit more generalized case

yogi_Time Calculations When Some Times Are Not In The same Day

                                          Google Spreadsheet   Post  #1154
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 25, 2013
user Jason Nargi :(http://productforums.google.com/forum/?zx=crpyw0e3sdva#!category-topic/docs/spreadsheets/Y0z1gCqSanQ)
Tallying times in sheets
We use sheets to track operating times for a small business jet.  We use the 24 hour clock (UTC) to calculate times which we then convert into tenths (1:30=1.5). At present we calculate times that go over the 2400 mark by assuming that there are 25 hours in the day.  An example of the problem would be to tabulate a time starting at 2230 and ending at 0030 the next day.  How can we write the formula to tabulate the time when it goes past 2400?  Thanks
---

Hi Yogi,

https://docs.google.com/spreadsheet/ccc?key=0Ar1uJjYjaMIKdFl6YzQxZjhVNHc1Qjk2NVhGSWhad3c&usp=sharing

Explanation;

In columns D, E and F we are inputting start times in 24 hour format.  In columns H, I and J we are inputting end times in 24 hour format.  Example; H1 - F1 = L1. In order for us to make it work we have to write the hour 0110 as 2510.  We would like a formula that resolves the need to write the number as 2510 instead of the actual time 0110.
Thanks
Jason
-------------------------------------------------------------------------------------------------------------------------------
here we go

yogi_From A Table Of Dates And Amounts Compute Average Amount For Each Month Row By Row And Then Average Amount Per Month

                                          Google Spreadsheet   Post  #1153
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 24, 2013
user T Sull :(http://productforums.google.com/forum/?zx=crpyw0e3sdva#!category-topic/docs/spreadsheets/zOjwEJgCj2Y)
Averging Spending Per Month
Hello,
  I have a list of purchases, and I would like to average the amount I have spent per month.

For example

Date:          Cost
1/01/2013    $10
1/15/2013    $15
2/24/2013    $14
2/24/2013    $18
2/28/2013    $21
3/05/2013    $13
3/14/2013    $16

I have been searching and can't seem to find/figure out an easy solution. I am somewhat unfamiliar with all of the native functions too.

Basically I want the formula to extrapolate the average cost for each month from two columns of purchase dates and then average out those averages to give me an actual average spent per month.

Any ideas? I know I can make a hidden table, but was wondering if there was a formula with nested formulas that could accomplish this.

Thanks :)
-----------------------------------------------------------------------------------------------------------------
following is a solution to the problem