Google Sheets Formulas – How to Run Formula Checking Multiple Cells

formulasgoogle sheets

I want to run a formula that checks each cell between say F2 and F8, and if they match the relative cell in column B, it adds those cells together, and outputs in cell F9.

Example expected result:

|B2 - 5 |  |F2 - 10|
|B3 - 6 |  |F3 - 6 |- Match
|B4 - 9 |  |F4 - 9 |- Match
|B5 - 12|  |F5 - 15|
|B6 - 2 |  |F6 - 2 |- Match
|B7 - 14|  |F7 - 14|- Match
|B8 - 1 |  |F8 - 7 |
|B9 -   |  |F9 - 31|- Output Cell

Edit: I left the part out where I ask if this is even possible. Silly me. So yeah, is this sort of formula possible on Google Sheets, or am I better off trying something else?

Best Answer

The formula you would use is:

=ArrayFormula(SUM(IF($B2:$B27=K2:K27,$B2:$B27,"")))

Functions used:

Formula

=ARRAYFORMULA(TRIM(SPLIT(JOIN(" ,", A1:A4,C1:C4), ",")))

Explained

You already showed us that the SPLIT function ignores empty cells, therefore I choose this string as delimiter: " ,". The intermediate result of the JOIN function, looks like this:
enter image description here

I choose the delimiter for the SPLIT function to be this: ",". The intermediate result of the SPLIT function, looks like this:
enter image description here

Now, however, we have to deal with the extra added white spaces. Therefore I used the TRIM function, to remove all trailing or leading white spaces (in combination with the ARRAYFORMULA). Compare the LEN values of each intermediate result.

Result

enter image description here

Example

I've created an example file for you: SPLIT to preserve blank cells

Google Sheets – Cell Concatenation by Hotkeys

I suspect that a third-party application or browser plug-in is interfering with the Windows clipboard and causing your problem.

Whatever the issue is, you may be able to quickly accomplish your copy/paste goal by using the Web Clipboard tool inside of Google Docs. (Look for the collapsible section called "The web clipboard tool".) The directions are not well written, though. Basically, select A2:A7, click Edit/Web clipboard/Copy. Select your destination cell. Click Edit/Paste (or Paste Special if you prefer. In fact, a few years ago I had a copy/paste issue that I could only resolve by using Paste Special but I cannot remember the details).

Because the Web clipboard goes through the Google server, if it does not work, then submit a bug report.

I believe it will work, though. If it does work, you can troubleshoot the cause of the problem by attempting the same action on another computer, on the same computer but in a different browser, on the same computer but with all background programs disabled (especially anything that looks at your clipboard such as anti-virus, screen capture, MS Office, or a clipboard manager), same browser but with plugins disabled, and/or copy the cells and then paste into notepad (it should put the data on six lines. I thought of at least one more troubleshooting idea but it's 2 am and I forgot it while I was writing the list. Oops. Try opening and editing the spreadsheet in LibreOffice or some other spreadsheet software.

I'm also curious what would happen if instead of selecting only B2, you selected B2:B7 and then pasted.

A potential problem is if you have multiple languages and keyboard inputs installed on your computer. When Windows switches from left-to-right languages to RTL languages, whitespace characters often do crazy things. So, you probably want to avoid selecting Arabic or other RTL languages while editing. If you have MS Maren or the language bar enabled, they might mess with you, too.

In the spreadsheet, is there anything in Script Manager?

I'm either out of ideas or too tired to think of more things. Good luck!

Best Answer

Related Solutions

Google-sheets – “Split” delimited string, preserving blank entries

Formula

Explained

Result

Example

Google Sheets – Cell Concatenation by Hotkeys

Related Topic