How to create a formula in Google Sheets that can sum specific cells based on multiple criteria

I am wanting to create a formula that will look at a dozen different cells and sum to the total value of those cells but some cells will not be numbers and I want to exclude those cells in the sum.

For example:

D2=10
H2=IF(B1="BYO","Incl.",5)
M2=IF(A1="standard","Incl.",15)

then I want to have Z2=SUM(D2+H2+M2) but sometimes the values will be numbers other times they will be text.

I was playing around using IFS and trying to use syntax like <> “Incl.” but I couldn’t figure out how to make it work with different cells that may or may not be numeric.

Email non-empty cells in tabular format

I’m trying to trigger an email to send all data on a sheet in columns B:F. I’ve found the tutorial below quite helpful but can’t quote get my head round it.

https://developers.google.com/apps-script/articles/sending_emails

/**  * Sends emails with data from the current spreadsheet.  */ function email() {   var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");    var startRow = 1; // First row of data to process   var numRows = 2; // Number of rows to process   // Fetch the range of cells A2:B3   var dataRange = sheet.getRange(startRow, 2, numRows, 6);   // Fetch values for each row in the Range.   var data = dataRange.getValues();   for (i in data) {     var row = data[i];     var emailAddress = "abc@cba.com"; // First column     var message = row[1]; // Second column     var subject = 'Jobs in Next 5 Days';     MailApp.sendEmail(emailAddress, subject, message);   } } 

What would I need to change that to in order send all non blank data in in B:F on Sheets in one email but keeping the tabular spreadsheet format?

What are good ways to present an info table that has info spanning 2 vertically-adjacent cells in a middle column, & that has no other merged cells?


Explanation of question

The following table illustrates the issue:

HTML table that doesn't present info clearly enough

HTML source for table:

<table>  <thead><th>Column 1</th><th>Column 1</th><th>Column 3</th></thead>  <tr><td>Type 1</td><td rowspan="2">Common Info</td><td>Info 1</td></tr>  <tr><td>Type 2</td><td>Info 2</td></tr> </table> 

You’ll notice that it isn’t clear that the non-header cells in the first column map to their corresponding cells in the third column, because of the cell merging. How can such correspondence be made clear?

Anyone have any advice regarding this situation?


Possible Solution

If I could mark-up the following table design using HTML, such that the cell entries were not images, then I believe such presentation would be acceptable:

Design of table for presenting the unusual information in clear way

Unfortunately, I didn’t use HTML to create the above design—I just used a simple raster graphics editor (Windows Paint) to create it.


Background

Whilst all compatible answers to this question are welcome, for myself, really, I want to find out how to mark-up such information using GitHub Flavoured MarkDown. See here for more information on the specific situation I’m trying to deal with, that prompted this StackExchange question.

Comparing two cells and adding check-mark to matching values

I have two columns, one with all items in a set and another with the items I currently have. I would like to compare those and for the matching results to have a bit of text added if they match. Here is an example.

So in that example, Column A is the set number, Column B is the set values, Column C is what I have currently, and Column D is the expected result.

So I want to compare B to C and if their are names matching, then I want B to have the word ‘CHECK’ appended to the matching name.

I thought about doing this to get the word ‘CHECK’ added

=ARRAYFORMULA(B2:B & " -CHECK") 

But do I also need a LOOKUP and LEN function here?

Would apps script maybe be easier?

Thanks for any advice,

Google Sheets | Dynamic Master sheet that grabs red formatted cells from another sheet

Title may be a little too ambiguous but I’ll try give a run down as best I can;

I have a Google sheet with multiple “Sheets” or Tabs (as I’d like to call them) within it.

The Main tab is blank and the rest have various cells with different dates on them. The dates that are close turn their respective cells Red. I have existing conditional formatting to do this.

What I want to achieve is the main tab basically checks all the other tabs for any red cells and pops them into the main sheet in a vertical column.

Here’s an example:

“Second tab”

  • (A1) Date 1 (green)

  • (A2) Date 2 (red)

  • (A3) Date 3 (red)

  • (A4) Date 4 (Green)

“Main tab”

  • (A1) Date 2 (red)

  • (A2) Date 3 (red)

As you can see, “Main tab” ignores any green cells from “Second tab” and only grabs the red ones. In my case though, there are many more tabs, not just 1.

I have a feeling “=IMPORTRANGE” may help in this scenario? Maybe.

Any help is greatly appreciated!

#Name? Error in Random Cells in SharePoint List

I have a custom list in SharePoint, with this formula in a caculated field.

=IF(QC_TimeStamp=””,””,CONCATENATE(TEXT(HOUR(QC_TimeStamp-Created_Modified),”#”),” Hr(s) “,TEXT(MINUTE(QC_TimeStamp-Created_Modified),”#”),” Min(s)”))

Some rows surfaces the correct time stamp and some rows return “#Name?”. Is there a way to fix the cells that are returning the error?

AppleScript for Excel to select/copy all cells?

Applescript can tell Excel to select a specific range of cells, e.g.

select (range "A1:B20")

and you can copy cells:

copy (range "A1:B20")

However, how can you tell Excel to select all the cells in a worksheet, or to copy all the cells in a worksheet? It seems to require a specific range. However, of course, when you are working with Excel directly you can just press cmd+A to select all.

Google Sheets Cells don’t calculate when printed or viewing as PDF

Workbook: Very large workbook with multiple tabs of data and multiple tabs of reports using sumproduct, query and filter formulas. Tabs

Payroll tab is a reflection of our master database so i’m not dead in the water; however, here’s my issue:

Query pulls data from multiple tabs (temple log, killeen log) using conditions to pull specific data. Here’s a look: Query Example

There are some hidden columns of data, but Mid, End, Wash, % Bonuses are all calculated fields off of the query data to the left columns. Everything in the screenshot above is showing and calculating correctly. When I print the sheet, that’s when things change. Formatting stays the same but some of my cells do not calculate.

Using Google Chrome with built-in PDF viewer. Also tried disabling built in PDF viewer. Chrome: Version 35.0.1916.114

The highlighted field (CPS Bonus) shows a 0 when viewing PDF and sometimes the % Bonuses have not calculated either.

Example:

enter image description here

As you see a stark difference.

My opinion is the sheet is calculating but because it takes a moment to pull all the data then calculate, Sheets generates the image for the sheet before the calculates have occurred.

Example query formula pulling data – this works flawlessly:

=if(Q1="","",query(vmerge('Temple Log'!A:Z,'Killeen Log'!A:Z),"select Col3,Col4,Col2,Col8,Col26,Col14,Col6,Col10,Col21,Col22,Col24,Col23 where (Col3='" & Q1 & "' or Col4='" & Q1 & "') and (Col11 is not null) and (upper(Col24)='M' or upper(Col24)='W' or upper(Col24)='E') ",0)) 

Example formula in CPS Bonus (does not show when PDF is generated):

=if(isblank(Q1),"",   IF(countif('Temple Log'!$  X$  2:$  X,"=W")>0, sum(iferror(filter(CPS!B4:B32*40,CPS!A4:A32=Q1)) - iferror(filter(CPS!D4:D32*40,CPS!A4:A32=Q1),"")))) 

Example formula in % Bonuses (sometimes these do not show when PDF is generated):

=        if(AND($  P$  30>0,I3>0),if($  E$  30 >= $  U$  7,if(I3*$  V$  7*E3 >M3,I3*$  V$  7*E3-M3,"")if($  E$  30 >= $  U$  6,if(I3*$  V$  6*E3 >M3,I3*$  V$  6*E3-M3,""),"")), "") 

Anybody have any ideas for work-arounds?

Google Sheets Conditional Formatting not working for all cells

This is my first question using StackExchange, so please bear with me. I am making a spreadsheet for tracking due dates for training. Sheet 1 shows due dates that people with view only capabilities can see. Sheet 1 has all of the conditional formatting and formulas referencing all information to Sheet 2. Sheet 2 shows the last dates that the training was done, which will only be viewable by editors of the spreadsheet.

Quick Rundown: Training A is good for 2 years.

Training B-E has to be done between April 1st and March 31st cycles, so if training is within those dates, then the next due date will show March 31st of the next year.

Training F is good for 1 year.

Training G is good until December 31st of the following year

Training H-J is semi-annually, so if it was done anytime within the first quarter of the calendar year, next due date is the last day of the third quarter. If it was done anytime within the second quarter, due date is the last date of the fourth quarter, etc.

Training K-M is good for 1 year.

Training N-P is good for 2 years.

Training Q-Z is one-time training, so no due dates and is just referenced to the cell on Sheet 2.

So, the problem I’m having is with the Conditional Formatting.I originally had one set of formatting for the entire sheet, but when I noticed certain cells weren’t highlighting the way they were supposed to, then I broke up the conditional formatting into groups.

Expected Results

-highlight yellow if date is between 4-6 months from expiring

-highlight orange if date is between 2-4 months from expiring

-highlight red if date is between 0-2 months from expiring

-highlight black with red letters if date is expired

-highlight cyan if date is today

Actual Result

-No highlighting in columns D-G and I-L

Is there anything that is preventing this from happening? Should I be using any different formulas for the results needed?

Here is the link: https://docs.google.com/spreadsheets/d/1gMC6vuCfjPuDMHpGw43sRheYxCqi09xGqBqW155gUjA/edit?usp=sharing

Random paths from one point to another going through all the cells of a square grid

I am looking for a very specific algorithm, so I think it doesn’t exist yet. I would be satisfied if anyone was able to give me some hints to develop it.

My problem is about a square grid of size n x n, where n is an odd natural number ≥3. For instance, it can be a 5 by 5 grid:

.---.---.---.---.---. |   |   |   |   |   | .---.---.---.---.---. |   |   |   |   |   | .---.---.---.---.---. |   |   |   |   |   | .---.---.---.---.---. |   |   |   |   |   | .---.---.---.---.---. |   |   |   |   |   | .---.---.---.---.---. 

The middle cell of the first column and the middle cell of the last column must be found:

.---.---.---.---.---. |   |   |   |   |   | .---.---.---.---.---. |   |   |   |   |   | .---.---.---.---.---. | ● |   |   |   | ● | .---.---.---.---.---. |   |   |   |   |   | .---.---.---.---.---. |   |   |   |   |   | .---.---.---.---.---. 

Then, and this is the hardest part of the algorithm, I want to get a path from the left-most point to the right-most one, that goes through every cell of the grid.

For example, this is one possibility:

  > > v   > > > > v   ^   v   ^       v   ^   v   ^   v < <   ^   v   ^   v       ●   v   ^   v   ●       v   ^   v   ^   v < <   ^   v   ^   v       ^   v   ^   > > > > ^   > > ^  

and this is another:

  > > > > v   > > v   ^       v   ^   v   < < <   v   ^   v       ^   v   ^   v   ● > ^   v   ^   ●           v   ^       v < < < <   ^ < <   v               ^   > > > > > > > > ^  

This implies directly some obviousnesses. Let a be the number of up displacements, b down, c left and d right, we can know that:

  • a + b + c + d = n^2 - 1
  • a = b
  • d - c = n - 1

I also want to randomize the creation of this path, and the best would be a uniform distribution amongst all the possibilities (I don’t know their number but I am almost sure it increases as n increases).

I think it can be solved thanks to graph theory since my grid can be considered as an unoriented graph where the vertices are the cells and where two adjacent cells are linked with an edge. The problem is now equivalent to making a non-redundant graph traversal, but I have no idea where to begin.

I hope my question was clear enough, thanks in advance.