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?

yogi_Unique Values From Multiple Columns Based On Values In Another Column

Google Spreadsheet   Post  #2463

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

question by: classroom in school
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/2AmNIytMxiw;context-place=forum/docs
Unique list of names from multiple columns and based on value in another column?
https://docs.google.com/spreadsheets/d/1vizdH7_Eb5E7f1aHZY6TX_P6xqdxOhqMked2us1EXFI/edit?usp=sharing

I have three columns:

A2:A6 contains letters like A or B

B2:B6 contains names

C2:C6 contains names

A name  can appear in both column B and C, like "joe" or "ivan".

How can I create a list with unique names, where the names only appear on the same row as letter B in A2:A6?

yogi_Compile Monthly Statement By Item From Monthly Expenditure Sheets

Google Spreadsheet   Post  #2463

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

question by: Edison Wu
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/Cs2NyUuZVI0;context-place=mydiscussions
Data validation question

Hi all,

I want to make a budget spreadsheet. I made a data validation drop-down list of the items so I can choose, for instance: I choose "Grocery" from the drop-down list and enter the value next to it. 

But is it possible to store the value and output the value on another sheet. Like I put $25 next to "Grocery" on day 1, and put $10 on day 2. Is it possible to show the sum of the value on "Grocery" and output it?

Thanks a lot.

Regards,

Edison

yogi_Prepare A Consolidated List Of Certifications Earned From Data In 'Form Responses 1'

Google Spreadsheet   Post  #2462

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

question by: dlrbestpublic
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/79stcxty14o;context-place=mydiscussions

How to create an array that omits blanks and writes to one cell?

Need some help please. I am using a google form to input certification into a Google Sheets spreadsheet. The form answers get written into a worksheet named 'Form Responses 1'. That tab and my Master tab have a Member ID column for matching purposes. 

Right now, I have no problem copying the first matching line (using Index/Match) from the Form Responses 1 tab into the Master tab to populate that member's certifications. 

However, as I have a few folks that are not familiar with Excel/Google Sheets, I would like the Google Form to be used for certification updates as well -not just a one time entry per member. For example, on initial entry, Certifications 1 and 2 were recorded for Member 1. Then one month later, Member 1 received Certification 3. Using the form to input that adds another line to the Form Responses 1 tab for Member 1. I would like to create an array of all entries for Member 1 and for each cell that is not blank, write that information into one single cell on the Master tab. So far, I cannot figure out how to get it to disregard blanks and to write to the same cell. This is the base formula I have been working with: 

Column B has the Member ID in the one sheet and A3 has it in the other. Column J is just one of the 28 certifications that are being tracked. Let's call that Cert1. I am trying to write the responses found in J (knowing that all will be blank but 1) into cell I3 on the master sheet. I have tried using ISTEXT, <>"", isblank, but the answer is eluding me. Any help is most appreciated.

=ArrayFormula(IFERROR(INDEX('Form Responses 1'!J$2:J$999,If('Form Responses 1'!J$2:J$999<>"",(SMALL(IF('Form Responses 1'!$B$2:$B$999=$A3,ROW('Form Responses 1'!J$2:J$999)-MIN(ROW('Form Responses 1'!J$2:J$999))+1),COLUMNS(I3)))),""))

yogi_Compute Amount By Category By Month

Google Spreadsheet   Post  #2461

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

question by: Marc Zaldivar
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/sVyYglkxraI;context-place=forum/docs
Trying to set up multiple conditions with SUMIF or DSUM?

Hi,

I'm have a form that collects payments by different categories.  I'd like to create another sheet that shows sums for each of the categories within a certain date range.

For instance, Sheet1:Column A is Payee.  Sheet1:Column B is Date.  Sheet1:Column C is Amount. Sheet4: Column D is Category (let's say, CatA, CatB, CatC).

On Sheet 2, I'd like to sum any amounts that are between 5/1/2018 and 5/31/2018 in CatA on one row, CatB on another.  

I assume I need nested IF statements or a DSUM, but I keep getting an error. 

Thanks!

Z

yogi_Compute Percentage Of Persons Who Got Scores Between Specified Range Of Numbers

Google Spreadsheet   Post  #2460

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

question by: J_sj
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/mh-DoJ_Jljs;context-place=mydiscussions

Simple percentage-calculating. (arrayformula?)

Hello, dear friends of higher understanding. 

I actually have two problems, the first one is my real problem, and the other one is explaining the first problem. The title isn't great, I know. 
Okay, so heres the deal, I have a spreadsheet looking like this:

The rows 5-19 each contain the scores for different players on 10 questions (1-10, G:F). 1 row is one player. Simple, right?
Now, here's my problem: I would like to make some simple statictics, like how many of all players (In percentages) get between 0-5 points,  how many get 6-10  points etc on these 10 questions.
So I need to:
1. Add up the points from G:F for each row.
2. Check how many rows that get between 0-5 points, how many that get 6-10 etc.
3. Calculate how many percentages of all players that got between 0-5 etc.

At the moment, I'm using far to many cells for this. It is my belief that this can be done with few cells using arrayformulas or something, I just donät know how to. Does anybody know?

I hope I have made the problem easy to understand.

Have a great morning/day/night!

yogi_Sum Up Time Entered in B3:B As Minutes And Seconds Without The Colon Character For Ease Of Entering

Google Spreadsheet   Post  #2459

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

question by: JoLR
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/FQa7QUUWdSE;context-place=forum/docs
Custom Time Formatting Help

Hello! I work in film editorial. I'm looking for a way to enter a time in minutes and seconds, and have it display as "xx:xx" - I'd like to enter this value as either 4 consecutive digits (i.e. no colon), or as minutes.seconds (e.g. 03.21). The reason being, I'm entering a lot of data, and it's a hassle to have to enter a colon when I just want to keep my right hand on the numpad.

Is there a way I can create a formatting rule, so that entering "0210" displays as "02:10" and correctly registers and minutes and seconds? Because I also have a function that adds the sum of this column so I have a total duration at the bottom.

Please let me know if that's clear. Thank you for your help!

JoLR

yogi_Create Top List From Table In A4:B7 With Scores And Names In Descending Order

Google Spreadsheet   Post  #2458

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

question by: Jesper_S
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/lvwDq5sPzfU;context-place=forum/docs

Creating a toplist

 

Good morning/day/evening!

I have a table "score"(A3:B7). In this table, I have the scores of four different people. From this I would like to create a toplist, looking like the one in the picture (A10:B14). 

I need a function that places all the people with the same scores beneath eachother, a.k.a. "John" in B11, "Jessie" in B12 and "Emma" in B13. How should I do?

I have managed to put John in B11 and Abby in B14 by using a combination of index() and large(). The problem I get, however, is that John gets put in B12 and B13 aswell, so, yeah, help?

Cheers!

//Jesper

yogi_query a column of single cells and output it to a column of merged cells

Google Spreadsheet   Post  #2457

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

question by: Blorty Blortson
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/R3Ku2m0BbT0;context-place=mydiscussions

How do I query a column of single cells and output it to a column of merged cells?

Hi 

everyone! 

I have am trying to import some data into merged cells (3 cell height). I need the merged cells because they are effectively labels for 3 rows. The problem I am having is that the range being queried is not merged so some data is being "hidden" in the merged cells; 1 cell appears and the other 2 are hidden by the merge. 

EX:

Source:

Data1

Data2

Data3

Data4

Current Output:

Data1

Data4

Desired Output:

Data1

Data2

Data3

Data4

My query is a standard =query(importrange("url","tab!range"),"select * where Col1 is not null")

I have been looking for about an hour but can't find anything of help. Is it possible to do what I am attempting? 

Thanks in advance! 

Edit: What I am hoping for is to have only one value per merged cell. So if A1:A3 and A4:A6 are merged cells, the data from the query would go into cells A1 and A4.