With almost universal availability of Internet and the availability of reliable on-line productivity tools, such as Google Docs, individuals as well as companies are switching over to Cloud Computing. In this blog I will post items of interest to my colleagues/patrons/clients.
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
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!
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
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.
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.
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
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?
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.
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?
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.
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.
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!
How do I query a column of single cells and output it to a column of merged cells?
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.
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.