## Monday, June 30, 2014

### yogi_Do Multi-Conditional Count In Google Old Sheets

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-29-2014
post by : Sin Chan: (https://productforums.google.com/forum/#!mydiscussions/docs/4Z33LbUCCl0)
How to write countifs

there is no countifs in google sheet. I look online on how to write arrayformula but it isn't working.

In excel, my formula work =COUNTIFS(\$E14:\$E170,"Critical",G14:G170,"MET")

Anyone can help me to write the same formula in google sheet? I have 2 rows. I am counting row E with "critical" and rpw G with "MET" or "NOT MET". I will count separately on critical with met and critical with not met.

Thanks!

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

## Sunday, June 29, 2014

### yogi_Pull Multicell Values Using VLOOKUP Where LookUp Table Is Not Set Up With First Column as The LookUp Column

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-29-2014
arrayformula with 2 criteria filter
Hello all

I have a sheet like this:
On the column D I have this formula:

=iferror(filter(F:F;G:G=A2;H:H=B2))

What i want to do is to apply ArrayFormula to the above formula so it replicates all down the table.
One thing i have in mind is that there will never be 2 players at the same spot, so duplicates will never happen.

I have searching for 2 days already, and all sugestions i have found is for 1 critÃ©ria only, not for 2 (As my filter states).
I want to use ArrayFormula instead of iterating because i have another sheet with 3000 Spots, and he takes always a while to process.

Any help would be apreciated.

Thanks.
-----------------------------------------------------------------------------------------------------------------------------------------

## Saturday, June 28, 2014

### yogi_Filter An Imported Sheet To ONLY Display Rows With Duplicate Entries (First And Last Names Combined)

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-28-2014
Filter an imported sheet to ONLY display rows with duplicate columns
Hello,

I have used the following formula to import data from another spreadsheet:
=Query(importRange("THE_KEY", "Form Responses!C1:S1000"), "select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16, Col17",1)

I would like to know how to alter this to ONLY display data that has duplicate values in Col1 and Col2. Col1 is "First Name" and Col2 is "Last Name" and this is a registration sheet so basically, I want to create a filter to determine who has registered more than once so I may delete their duplicate records. Thanks!
--------------------------------------------------------------------------------------------------------------------------------------------

## Friday, June 27, 2014

### yogi_Count Column By Column (from A to D) Number Of Rows That House The maximum Value in that Row

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-27-2014
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-27-2014
Need Help: COUNT quantity of highest values in a row
Hey, I have the following table:

 A B C D 1 28.00 8.00 48.00 36.00 2 28.00 24.00 32.00 36.00 3 25.00 9.00 52.00 32.00 4 27.00 27.00 18.00 28.00 5 44.00 32.00 16.00 28.00 6 44.00 12.00 44.00 20.00 7 16.00 36.00 28.00 40.00 8 48.00 12.00 32.00 28.00 9 36.00 32.00 8.00 44.00 10 36.00 40.00 24.00 20.00

I want to count the number of values of a column, where this value is the highest number in that row. I marked the high numbers bold. So for A the count = 3, for B = 1, for C = 3 and for D = 4. I tried using COUNTIF, but cannot seem to find the correct formula. I want to display the count below in a new row. Which is the correct formula to COUNTIF, this value =MAX in the respective row?
-------------------------------------------------------------------------------------------------------------------------------------

## Thursday, June 26, 2014

### yogi_Pull Values Into Another Column Value In Cell B3 And Then Every Specified Number Of Rows Below That

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-27-2014
=cell + 7 down
I have a cell in B3 for example, and the next relevant cell is 7 cells down from that, B10. How can I write a formula that would =B3 +7cellsdown?

It would then look like this:

=B3
=B3+7cellsdown
=B10+7cellsdown
=B17+7cellsdown

Thanks.
------------------------------------------------------------------------------------------------------------------------

## Sunday, June 22, 2014

### yogi_From Results Of ImportJASON Contents Of Which Contain URLs In Sheet Named 'blacklistUrls'

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-22-2014
this is an additional solution to the one I had provided in my following blog post
yogi_Pull Data By Matching A Column In The Spreadsheet With A Column in Another Sheet (spreadsheet)
http://yogi--anand-consulting.blogspot.com/2014/06/yogipull-data-by-matching-column-in.html
I have provided solution presented here in response to a comment from Carl:

"Once again, a big thanks for everyone's help, here's a template for the end goal.

Im having some problems moving the formulas around to display in the correct positions.

Is it possible to paste a JSON Url in a cell, then the JSON formula can reference it and then list results?"

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

## Saturday, June 21, 2014

### yogi_Pull Data By Matching A Column In The Spreadsheet With A Column in Another Sheet (spreadsheet)

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-18-2014
I currently have 2 spreadsheets, one with a imported JSON list of urls.  another with a list of blacklist sites.

Imported JSON URL Sheet 1 is url formatting is: http://xxxxxxxxxxxxxxxx.com/xxxxx/xxxx.xxx
Blacklist Sheet is formatted: xxxxxxxx.com

I would like the end result to be just a list of blacklisted links from the JSON urls.

I haven't used sheets much but I'm keen to learn.

---
Hey ya Bob,
Here's the JSON in a sheet with an additional column added to check out Ivanna's suggestion.
Here's the blacklist reference sheet

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

### yogi_Use VLookup Function To Pull Data From Form Submissions Into Another Sheet And Then Sort It In Descending Order

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-18-2014
VLookup problem - cells not populating and formula changes upon data entry
June 21, 2014

Looking for Google sheets help with this question:

Operating system:  Windows 7

Here is the link to the sample form.  This is a sample of a problem I'm having with a real spreadsheet and job applicants for teaching positions.

On the resulting spreadsheet I have 4 tabs.  In the sample, I have the following tabs:

​Tab 1: Form Responses 1 (captures data from the live form)
Tab 2: Sample Roster (copies another spreadsheet with staff information)
Tab 3: Eligible Applicants (uses vLookup to get information from the Sample Roster based on the OCT# from Form Responses 1)
Tab 4: Sorts the results from Eligible applicants based on a variety of criteria

My problem is that when the spreadsheet is first created everything works well.  However, once new data is entered from the form, Tabs 1-2 work, but tab 3 does not show any new entries, and the formula changes on the spreadsheet where the data should have been entered.

You will see on cell 8 of Eligible Applicants, the data does not show up and the formula changes to D9 where it was originally D8.

Any thoughts?  One thing I was thinking is that despite the spreadsheet data being formatted as plain text, the data from the form coming in is not showing as plain text?

Inspired education. Inspiring students.
------------------------------------------------------------------------------------------------------------------------------------------------------

## Tuesday, June 17, 2014

### yogi_Find Amount For A Specified Number Based On A Given Table Of Percentages

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-18-2014
Finding a Percentage based on amount. Up to 1800 50%, 1801-3600 60%, 3601 on up %70
I need a formula to help me figure out different percentages.
Anything up to 1800 I need 50%
1801 through 3600 I need 60%
3601 on up I need 70%

So if the number is 6600 I need it to come out to 4080 (1800*.5=900, 1800*.6=1080, 3000*.7=2100)

I am hoping to have one formula to make that all possible.

Daniel
-------------------------------------------------------------------------------------------------------------------

## Monday, June 16, 2014

### yogi_Conditional Formatting For Dates In Column B - Compared To Reference Date red if within 7days -- yellow if within 14 days -- green if over 14 days

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-16-2014
Conditional Formatting
How can I specify 1-4 weeks vs. entering specific dates? I want to avoid having to update the date field on a weekly basis. Thank you!
---
I would like for the background of the DUE DATE to automatically change from green (14+ days from due date) to yellow 14 days before due date & from yellow to red 7 days before due date.

B1 should have a red background because it's within 7 days of due date
B2 should have a yellow background because it's with 14 days of due date
B3 should have a green background because it's over 14 days from due date

See attached...I apologize for not being clear. I hope this helps.

Thanks much!
Lissette
Attachments (1)
Test - Sheet1.pdf
----------------------------------------

## Sunday, June 15, 2014

### yogi_Compute How Many Pax Each Lecturer Has Form A Table Of Lectures With A Lecturer Being On More Than Once Per Day

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-15-2014
Sum of people that has attended a week of seminars hosted by specific lecturers
Hi Forum,

In the below spreadsheet I'd like to know how many Pax each Person has had on their seminars these 5 days.  I've tried to use VLOOKUP but that only works for days when each lecturer only did 1 seminare.  Lecturers never do more than 2 seminars in a day.  I've experimented with ARRAYFORMULA, SUM and VLOOKUP and failed miserably...  I have a feeling there is an easier solution since this isn't exactly a very strange situation...

I'd like to have a resulting list like this:
Kind regards,
Matthias
-------------------------------------------------------------------------------------------------------------------------------------------

### yogi_ Copy Data From Some Rows Of Sheet1 Of Main Spreadsheet So That Data Can Be Edited Without Affecting the Data In Main Spreadsheet

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-15-2014
Script for copying new rows to another spreadsheet
I would be most grateful for your help. I have a spreadsheet which gets updated with form submissions. I need to keep this intact. So I created another copy of this spreadsheet which is a separate file - not linked to the original spreadsheet. I know I could use importrange to update the copy of the spreadsheet, but I don't want to use it because it would not be possible to edit the copy. I have been trying to find a script that would automatically update the copy with new rows from the original spreadsheet as it gets new submissions, but failed miserably. Can you help? I very much hope so.
---

I have created two spreadsheets (old Google sheets) called Main Sheet and Copy of Main Sheet.
The Main Sheet has columns A to G with headers and four rows of data.

The Copy of Main Sheet has columns A to F with two rows.The last column F is not in the Main Sheet as it is intended only for the Copy of the Main Sheet - it is configured with data validation.

here is the link to the Main Sheet:

and here is the link to the Copy of Main Sheet:

I would like to copy the additional two rows or any other subsequent rows automatically from the Main Sheet to the Copy of Main Sheet - but only from columns A to E (Timestamp, First Name, Surname, Gender, Pets? in the Main Sheet.

I know I could use the importrange formula like this =importrange("spreadsheet-key","Sheet1!A:E"), but that would not allow me any edits. The point is that I would like to be able to edit the Copy of Main Sheet without affecting the Main Sheet and add additional information into the remaing column F or any other columns I might insert into the Copy of Main Sheet later.

I hope this makes sense? I don't have any other formulas I am afraid.
I very much hope you'll be able to help.

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

## Friday, June 13, 2014

### yogi_Use VLOOKUP Function For A String Of Comma Separated Values

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-13-2014
VLookup with CSV in input cell?
Hello!
I need to perform a VLookup function that has comma separated values as input, the output ideally would also be comma separated.
Here is sample data:

Range List
TKA   TKAimg.jpg
TKF   TKFimg.jpg
TKWO  TKWOimg.jpg
TKMO  TKMOimg.jpg
...

For input:
TKA, TKF

I need output:
TKAimg.jpg, TKFimg.jpg

=VLookup(input, range, index, FALSE) works fine with a single input value, I found some forum posts about a MultiVLookup function but Google Spreadhseet gives me a #NAME? error "Unknown function".

Any idea how to solve this?

I thought I could have a few intermediate columns that split the input column into single values and then concatenate the outputs... but that adds a lot of "no-touchy" parts to my document, and rises more questions (e.g. how many values max will people concatenate?)

Thanks

Elena
---
Thanks Yogi!

My reference values are in table A1:B7, License and Media.

In table E1:F4 I have tried simple VLookup with the following formula:

=VLOOKUP(E2A2:B72FALSE)
and with single "License" type input (such as TK F or TK CO) in column E I do get Media output in column F. This one worked just fine.

Table E6:F8 shows what I am trying to accomplish, I expect users to add comma separated License type input, the kind you see in E7 or E8. For these, I would expect in F7 and F8 the following output:

E7 = TK F, TK A

E8 = TK CO, TK MR
F8 = lc_label_communityuse.pnglc_label_men.png

I tried the following formula:
=MultiVLookup(E7A2:B72FALSE)
but as I said I get an error, so I'd love to be able to accomplish this just with VLookup like you say.
Thanks a lot!
Elena
--------------------------------------------------------------------------------------------------------------------------------------------

### yogi_Pull Columns For 'Person 1' Only From 'PlatForm 1' That Contains Columns For A Number of Persons

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-13-2014