yogi_Return 'NOT' If Range D2:D14 Matches Specified Text Otherwise Return 'OKAY'

Google Spreadsheet   Post  #2469

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jul-01-2018

question by: Dark Sign
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/mLPY6RsAV0s;context-place=forum/docs

if any cell in a range is anything else, return a different result?

can someone help with this, I am trying to get a specific result when a range of cells contains some specific word but also some random ones,

I want to get a different result if the range contains any other content

test file : https://docs.google.com/spreadsheets/d/1kcfglvpIj3KUIVev3mCD9fstF15zbHDJECLQbcZx-Xs/edit#gid=0

yogi_Clean up a column of data and compute the average of numbers from row 2 down

Google Spreadsheet   Post  #2468

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jul-01-2018

question by: meph2u
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/278SDB2MajE;context-place=mydiscussions
Clean up of a column of data to be able to average it

I have a column of data which I want to average.   It needs cleaning as follows:

Anything with an F or F* becomes 18

Anything with a #* becomes the number

Anything with a # stays a number

Anything with a time becomes a 0

Here is an example:  https://docs.google.com/spreadsheets/d/1cPkMFrYrPti8KC018DHAk_NMwtMGaYCypjst7DW1_N4/edit?usp=sharing

What I am looking for is a single formula to put in K1 that will do the work of the formulas in L2, M2, N2, O2 and O1

L2:  =arrayformula((substitute(K2:K,"*","")))

M2: =arrayformula((substitute(L2:L,"F",18)))

N2: =ARRAYFORMULA(value(M2:M)) - this has a problem as it turns blank cells into 0 which messes up the average in O1. But ISTEXT and ISBLANK don't seem to help. For example =ARRAYFORMULA(if(ISblank(M2:M),"",value(M2:M)))

O2: =arrayformula(if(N2:N<1,0,N2:N)) (all times seem to be less than 1 after the "value" function is applied

O1: =average(O2:O) this would work if all blanks in M had not been converted to "0" with the value function in N2

Any good ideas for this one? Thanks

yogi_Multi-Criteria Count Problem

Google Spreadsheet   Post  #2467

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jun-30-2018

question by: guyute
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/dA21oBp67f4;context-place=mydiscussions

How can I count totals of items multiple columns when the items overlap in each column?

I'm wondering if you could help. I'm trying to record procedures being done by multiple people.  Ultimately, I want to get the total amount of each unique procedure each person has done.  They will do different number of procedures each time (i.e. sometimes just one, sometimes 2, sometimes 5, etc.)  

Here's an example of what I have set up

	Procedure 1	Procedure 2	Procedure 3	Procedure 4	Procedure 5
Tom	a	a	b	c	d
Jerry	b	a	c	d	e
Mickey	e	b	c
Minnie	a

Each procedure could be either a, b, c, d or e.

Tom, Jerry, Mickey, and Minnie are the people performing the combinations of procedures.

I want to total automatically each specific procedure (a, b, c, d or e) regardless of which procedure column it falls in.  In other words, I don't care if there are 3 "a"s in procedure 1 column and 2 "a"s in column 3.  I just want a way to get to the total of 5 "a"s for Tom, Jerry, Mickey or Minnie.  

Is there a better way to record the data rather than by creating 5 procedure columns?  If not, then is there a way to get a count of unique procedures in all 5 columns?

This is somewhat difficult to explain, so I apologize if I'm not doing it well.  Thanks for any help!

yogi_Get Count Of 'Work Codes' Withe Multiple 'Work Codes' Entered Per Row IIn Sheet Named 'Invoices'

Google Spreadsheet   Post  #2466

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jun-28-2018

question by: Mindi Bishop
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/Jr3IhjcPABQ;context-place=forum/docs
How to count multiple values
Hello!

I have a worksheet where on the "Invoices" sheet, there is a column that has Work Code letters.  Some are single letters, others are multiple. 

Invoice #	Work Code
18002	A
18003	F
18004	I
18005	G,E
18006	C
18007	F,B

 Then there is a "Work Code" sheet where I would like to count how many times each Code is used.  

Work Code	Work	Totals
A	Add Gore	1
B	Button	0
C	Patch Hole	1
D	Alter Sides	0
E	Elastic Replaced	0
F	Figment Costume	1
G	Hem Pants	0
H	Hem Skirt/Dress	0
I	Hem Sleeve	1

I am using this formula to count the work codes.  =COUNTIF(Invoices!$C:C,"A")

But as you can see, it does not recognize the cells that have multiple values in them.  

Can someone help me out?

Thank you!

yogi_Match FirstName LastName And Date In Sheet1 With Those In Sheet2 And Pull Those And Hours From Sheet1 Into Sheet3

Google Spreadsheet   Post  #2465

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jun-26-2018

question by: Ed W..
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/HkmwlsDJ2eM;context-place=forum/docs
Comparing 2 tabs and outputting value

Sample Sheet

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

I have a question regarding comparing multiple columns in 2 different tabs and outputting a value.  I have provided an example of what I am looking for.

Essentially I want to compare the First Name, Last Name and Date columns on sheet 1 and 2 and if all 3 values match between the two then output the hours from sheet1 into sheet 3's Hours column which I can then apply conditional formatting to.  I have tried a couple methods without luck and my Google Fu is failing me today.  I understand there may be easier methods to accomplish this but this is the constraints I am working with.

Essentially certain users would copy/paste data into sheets 1 and 2 for comparison which would be listed on sheet 3. The data from sheet 3 is nothing more than queries from sheet 2.

Any help would be appreciated and I apologize if I did not make this clear.

Thank you!

yogi_add values up from a data (from to hours) validation drop down box

Google Spreadsheet   Post  #2464

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jun-22-2018

question by: kyle hoelger
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/KOhWMJqU6cE;context-place=forum/docs
How to add values up from a data validation drop down box?
Hi,

I have created a rota with data validation drop down boxes with the times I went my team to work, at the end of the week in a separate column I wanted to add up how many hours that staff member has worked. so example is 8:00am - 16:00pm is 8 hours shift but i cant work it out how to get that cell which contains the value 8:00 - 16:00 to equal a number for example 8 hours. 

What I would like it to do is be able to use the data validation so I can drop down the times for each staff member and it all adds up the total hours worked in end column. 

Is there a way to make this happen?

I have attached a link to my document but all personal data has been taken out of it for reference.

https://docs.google.com/spreadsheets/d/16iveC8_DYM7v0UxXcaVxlkoWo_gdyjU2bFA7nvdhmjM/edit?usp=sharing

Many Thanks

Kyle

yogi_Pull Multiple MATCH results from one row

Google Spreadsheet   Post  #2464

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jun-22-2018

question by: Kelther
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/Nu5z3DJ3nJg;context-place=forum/docs
Multiple MATCH results from one row?
Is there a way to use Match to get multiple results on one row?

My data is as follows

Name     Apples     Bananas     Oranges     

Alice          1                 3                   4

Bob           3                 3                  2

Charlie       2                 2                  2

I'd like to put in a formula using VLOOKUP and MATCH to determine the following:

What does [VLOOKUP index] have [MATCH Index] of?

What does Alice have 4 of?

What does Bob have 3 of?

What does Charlie have 2 of?

Results:

Alice     Oranges

Bob       Apples      Bananas

Charlie   Apples     Bananas    Oranges

Is there any way to do that?