yogi_Count No Of Instances By Status Based On Multiple Criteria

                                           Google Spreadsheet   Post  #1970
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jun 25, 2015

question  by Kanagalakshimi Ravinttirin:

https://productforums.google.com/forum/#!topic/docs/u5hDKalwJPI;context-place=forum/docs

Count of Status based on 2 criteria
Hi all,

Hope you can help me out.

Maybe it is super simple, but I'm unable to figure it out.

I have this table:

Date	Tag	Ticket#	Status
5/25/2015	JMQBR42	156273	Work In Progress
5/25/2015	D17V0P1	156273	Work In Progress
6/3/2015	7NLQ9T1	156503	Resolved
6/3/2015	B50Q9T1	156503	Resolved
6/3/2015	DX78XS1	156503	Resolved
6/3/2015	3KTK6V1	156523	Resolved
6/3/2015	60LP8X1	156524	Work In Progress
5/29/2015	B8J2B2S	156551	Work In Progress
5/29/2015	6KMTRR1	156566	Resolved
5/29/2015	68H4VS1	156572	New
5/31/2015	GZR0QH1	156573	Work In Progress
6/1/2015	365D7M1	156750	Resolved

And expecting output such as

Expected Output
Date	No of Ticket	No of Tag	Status of Ticket
			New	Work In Progress	Resolved
6/3/2015	3	5	0	1	2
5/29/2015	3	3	0	2	1
5/31/2015	1	1	0	1	0
6/1/2015	1	1	0	0	1

I am all with success for No of Tickets and tags. But unable to do the counts for Status. Anyone?

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

yogi_Delineate 'Yes' Or 'No' Depending On Whether Set Of 9 Rows Starting From Row 3 Down Contain 'http' in Any Of The Rows Of Column A

                                           Google Spreadsheet   Post  #1970
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jun 23, 2015

question  by Josh Rosenberg:

https://productforums.google.com/forum/#!mydiscussions/docs/Pq4-EnhnLuI

How to determine whether "http" text appears within a range of cells in a column

yogi_For Values In Each Row LookUp Value In Column 2 Of LOOKUP TABLE And Concatenate Resulting Values Separated By A Comma

                                           Google Spreadsheet   Post  #1969
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jun 21, 2015

question  by Naveen Nagaraja:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/ZVw2xo9TbaE

Google Sheet search multiple values
Hi,

I have a google sheet question

I have search data as follows

X|  Y

1 | abc

2 | def

3 | xyz

I have a row of data as follows

A| B | C

1 | 2 | 3

Now I need to search data in columns A , B and C with search data table above ( column X and Y) and then have a cell filled with value

abc,def,xyz  

How can I do this in Google sheet

Thanks,

Naveen

---

Hi Hyde,

Thanks for the information. I was looking for the first solution and it works good.

Is there a way to add a NULL check to vlookup formula ( suppose there is column D is a empty cell and I want to include it ) ?

Thanks again!!

X	Y	A	B	C	D	G
1	abc	1	2	3		abc, def, xyz,
2	def	2	3	1
3	xyz

Notice Column D is Empty but still we try to compare and then in Column G we have a , due to this .

I tried ISBLANK function but could not make it work

Naveen

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

yogi_Compute For Data In Several Columns Row By Row Average Of Numbers And Associated Percentage

                                           Google Spreadsheet   Post  #1968
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jun 20, 2015

question  by AlexIR:

https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!category-topic/docs/spreadsheets/Ywg92-ccxRs

Stop Forms from shifting rows in Sheets
Hi everyone,

I have a small survey in Forms which asks for the participient's number of Facebook friends and the likes for his 5 recent profile pictures. Thus the colums in the Response Sheet looks like this:

Form Responses 1:
--------------------------------
A: Timestamp
B: # Friends
C: Likes Picture #1
...
G: Likes Picture #5
--------------------------------

For the calculations I have another sheet with three columns:

Calculation:
--------------------------------
A: #Friends: ='Form Responses 1'!B2
B: Average Likes: =AVERAGE('Form Responses 1'!C2:G2)
C: Percent: =(B2/A2)
--------------------------------

I wrote the Formulas into Row 2 and then copy-pasted them over all rows below.

Now here is the catch:
As soon as someone makes a new entry to the form, the respose inserts a new row to the Responses sheet, which also creates a gap in the calculations: 

Calculations:
--------------------------------
A2 = Form Responses 1'!B2
A3 = Form Responses 1'!B4--------------------------------

How do I stop this from happening?

Thanks in advance,

Alex
---------------------------------------------------------------------------------------------------------------

yogi_Compute Times For Specified Number Of Tasks Given Start Time And Number Of Tasks Per Hour

                                           Google Spreadsheet   Post  #1967
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jun 18, 2015

question  by TN Dispatch:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/vEoANsedz5g

Schedule Builder

Hello i am looking for a formula that will build be a schedule based on a start time and number of trucks per hr

so if the start time is 14:30 and i need 1 trk every 4 hours then i will get a schedule that looks like this

14:30

18:30

22:30

02:30

06:30

now if the current time hits 18:30 i want to see

18:30

22:30

02:30

06:30

10:30

say i change the trks per hr to 4 trks every hr and the schedule starts at 14:30 the schedule will look like this

14:30

14:45

15:00

15:15

15:30

and when 14:45 comes around it will show

14:45

15:00

15:15

15:30

15:45

when 15:00 comes around it will show

15:00

15:15

15:30

15:45

16:00

and so on i have attatched the spreadsheet and highlited in yellow where i want the values to appear and in green is where i will put the start time and trks per hr.

https://docs.google.com/spreadsheets/d/11-_zdXQMmr3_VHiPIamU320kvxhMNQxpWCfbdAUjMlQ/edit?usp=sharing

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

yogi_Compute Sum Up Numbers For Specified Number Of Months With Each Month Data Provided In Specified Number Of Columns

                                           Google Spreadsheet   Post  #1966
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jun 15, 2015

question  by mpacker:

https://productforums.google.com/forum/#!mydiscussions/docs/kB4Ca0yfyM4

formula to add every 3rd column to the left

i have a spreadsheet that has three columns for each month  we will call them x1,y1,z1  next month i insert three new columns after z1 and we will call them x2,y2,z2 and so forth.

there are a few columns at the very end and one of them i'm trying to get total for last 12 months of x.   i can do this with a formula i change each month but i'd really like some way to say take the column 3 to the left + 6 to the left + 9 to the left... till i get a total of 12 then when i insert to columns it automatically changes to the last 12...

any ideas?

hope that's understandable.

thanks!

---

Hi Yogi!

Here is a link to a sample of what i'm trying to do:

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

if you look at columns AL, AM and AN  those are always the last three columns.  Each month i add three new column (in this case AI, AJ and AK).

The formulas in AL, AM and AN are the ones i'm trying to figure out how to get a formula so that I don't have to edit each month.

The formula in AL3 is currently

=AH3+AE3+AB3+Y3+V3+S3

after AI-AK have been added what i really want AL3 to do is be the sum of V3+Y3+AB3+AE3+AH3+AK3  (basically dropping S3 and adding AK3)

same thing for AM and AN columns....  I'm wanting AL to AN to always be working off of the last 6 months.

does that make sense?  

I really appreciate the help.

---

I've modified the spreadsheet.

There are two tabs now.  

Month1 and "next month"

at the end of each month i add three columns.  the sheet would change from "month1" to what's in "next month".

when i do this i have to manually edit the formulas in ao3 to aq5

For example:

in month1:ao3 the formula is the sum of  v3+y3+ab3+ae3+ah3+ak3

when i add the columns next month i want a formula in ao3 to automatically change so i don't have to change ao3 to y3+ab3+ae3+ah3+ak3+an3 

the blue column should always be the sum of the 6 grey columns to the left of blue column.. when 3 new columns are added..

if played around with arrayformula and other things but i can't figure out how to write one that does this.

Thanks

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