yogi_Do Multi-Conditional Count In Google Old Sheets

                                        Google Spreadsheet   Post  #1685
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!
---------------------------------------------------------------------------------------------------------------------

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

                                        Google Spreadsheet   Post  #1684
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-29-2014
post by : Webix: (https://productforums.google.com/forum/#!mydiscussions/docs/cTkq3dGQQEI)
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.

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

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

                                        Google Spreadsheet   Post  #1683
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-28-2014
post by Regional Command: (https://productforums.google.com/forum/#!searchin/docs/C1$3AS1000/docs/FlXCIELSbdY/bOnvZzZjICYJ)
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!

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

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

                                         Google Spreadsheet   Post  #1682
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-27-2014
post by Stefan Kubler Palfner: (                                         Google Spreadsheet   Post  #1681
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-27-2014
post by Stefan Krause Palfner: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/KR2jFgOY6uY)
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?

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

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

                                         Google Spreadsheet   Post  #1681
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-27-2014
post by Aaron Stepanik: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/how-do-i/chrome-browser/no/WH-Z2wZgP_E)
=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.

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

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

                                         Google Spreadsheet   Post  #1680
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-22-2014
post by Carl Wicker: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/9XxtjVsCiko)
Filtering from another spreadsheet
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.

https://docs.google.com/spreadsheets/d/1gFgWpSCLTLQvfOENRdJrVGJB_qc7JkpiTl-BJloy4Jc

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?"
--------------------------------------------------------------------------------------------------------------------------------------------------------------

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

This summary is not available. Please click here to view the post.

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

                                         Google Spreadsheet   Post  #1678
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-18-2014
post by Tom D'Amico: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Y434D-EXeCg)
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

Browser:  Google Chrome

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?

Thanks from Canada,

ocsb.ca | Inspired education. Inspiring students. 

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

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

                                         Google Spreadsheet   Post  #1677
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-18-2014
post by Dapperdw: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/2YFdWD6fQ5w)
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.

Thank you so much in advance for your help.

Daniel

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

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

                                         Google Spreadsheet   Post  #1676
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-16-2014
post by Lissette Irizarry: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/fFoZZTI91u4)
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

22 KB   View   Download

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

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

                                         Google Spreadsheet   Post  #1675
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-15-2014
post by Matthias Stasaik: (https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!category-topic/docs/spreadsheets/oRT-Qm42YeQ)
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

                                         Google Spreadsheet   Post  #1674
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-15-2014
post by Lizzerle: (https://productforums.google.com/forum/#!mydiscussions/docs/9Y2L6N0PCIU)
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.
---
Thank you very much for your kind reply.

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:
https://docs.google.com/spreadsheet/ccc?key=0ArTs_MwgtoXydE5ZMkRkWXhad3lDYjJ4ajdCd0xCREE&usp=sharing

and here is the link to the Copy of Main Sheet:
https://docs.google.com/spreadsheet/ccc?key=0ArTs_MwgtoXydExIcVdubTRmdTdUblJvY2dlN2xlT2c&usp=sharing

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.
------------------------------------------------------------------------------------------------------------------------------

yogi_Use VLOOKUP Function For A String Of Comma Separated Values

                                         Google Spreadsheet   Post  #1673
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-13-2014
post by sonouna: (https://productforums.google.com/forum/#!mydiscussions/docs/x-gkF7KxeRE)
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!

Here is a working spreadhseet

https://docs.google.com/spreadsheets/d/1mGNpPH33qCTrm2OfLNrBMtXYNhzDz7VTvlOPrgl5OxA/edit?usp=sharing

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(E2, A2:B7, 2, FALSE)

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

F7 = lc_label_family.png, lc_label_attribution.png

E8 = TK CO, TK MR

F8 = lc_label_communityuse.png, lc_label_men.png

I tried the following formula:

=MultiVLookup(E7, A2:B7, 2, FALSE)

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

                                         Google Spreadsheet   Post  #1672
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-13-2014
post by jfregoe: (https://productforums.google.com/forum/#!mydiscussions/docs/OcTamzpxELM)
Can you add a horizontal filter to data in Google Sheets?
I have figured out how to add a filter so my sheet hides rows, but I am looking for a way to hide columns with a filter view. I'd like to be able to sort this information and set a few different filter views for the same sheet. I considered rotating the data so I could use the vertical filters, but it's much more difficult to review that way. Any suggestions are appreciated. 
---
You can find a sample of my document here. You'll see it's a schedule for my department. The sample is just for a week, but typically I would make it for an entire month. We have people who are trained to work in multiple areas and we need to be sure we have enough staff for all areas at all times. We don't have scheduling software and I can't get anyone on board with getting some, so while those suggestions are appreciated, it won't work for me at this present time :)

You'll see the "Master Schedule" sheet is a basic overview of who is scheduled. Typically, the following weeks would each be listed under the first one (for the sake of an example, I have only included 1 week). You will also noticed I have added some filter views to this sheet to demonstrate that they do work beautifully for this sheet.

The individual platform sheets are how I calculate the number of people we have working at a given time who are trained in a specific area. You will notice I have a column for each person on each day where we mark the times they will be working. I have a "countif" formula set up to calculate how many people are working for that platform. As you look through the different platforms, you will notice several employees are trained on multiple platforms. 

You will also notice that certain areas of this document are locked while others are editable. That is how I share the document with my staff. They have the ability to edit and update their schedule as needed.

Here is where the horizontal filters would come in handy: When staff need to update their schedule, they have to sort through an entire month's worth of information. You'll notice the filter views on the master sheet help with that, but those are vertical filters that hide rows. On the platform sheets I would like to set up a similar filter, except I want it to have a horizontal filter that hides columns. This way a person could choose their filter and only see their shifts to make necessary changes.
-----------------------------------------------------------------------------------------------------------------------------------