Single-cell array formula In cell H20 of the example workbook, type or copy and paste =SUM(F10:F19*G10:G19), and then press Enter. In this case, Excel multiplies the values in the array (the cell range F10 through G19), and then uses the SUM function to add the totals together.

October 14, 2025 - You can actually combine a text with a text, a text with a number, and a text with a date in Google Sheets and apply ARRAYFORMULA to that combination. For example, if we have a list of, let’s say, sales managers, and need to combine the first and last names.

Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays. Sample Usage ARRAYFORMULA(SUM(IF(A1:A10>5, A1:A10, 0

December 17, 2024 - For example, if A2 contains "Alan" and B2 contains "Smith," C2 will display "Alan Smith." Scenario: You have sales data in column A, corresponding regions in column B, and product categories in column C. Let's show you how to calculate the total ...

May 25, 2026 - For example, imagine you're tracking monthly sales and as you enter new sales data each month, the array formula will look like: =ARRAYFORMULA(SUMIF(B3:B11, ">=01/01/2023", C3:C11))

November 26, 2025 - Read more: Google Sheets vs. Excel: What’s the Difference? Close the formula by adding a closing parenthesis ) at the end. The complete formula should look something like this: =ARRAYFORMULA(G2:G14*H2:H14)

April 28, 2022 - However, I would have to input the function multiple times for each criterion, as VLOOKUP only returns a single value. However, when I combine this with the ARRAYFORMULA, I can find multiple values at the same time. ... In this example, I want to find the first name, last name and results for project A and B of student number 10.

October 8, 2025 - The COUNTIF function is quite similar to the SUMIF function. Let’s say the following dataset represents the price of some fruits in different regions. We can count the number of areas of each fruit by applying the COUNTIF function, wrapping the ARRAYFORMULA function to get the output at a time.

March 15, 2026 - The ARRAYFORMULA function can also wrap functions that return multiple values. ... This formula first filters the values in B2:B10 that are greater than 5, and then multiplies each returned value by 25.

September 23, 2024 - For this example, I have a table listing multiple transactions for three different products. Instead of calculating the total for each product, I can use SUMIF to calculate the total per product and ARRAYFORMULA to return an array with the three ...

June 20, 2024 - I’m trying to use the ArrayFormula to flatten a table, but I needed to leave the range conditioned to the last row where there’s data, so when I have a new entry, the flattened table is automatically generated.

September 2, 2025 - For this example, type: Multiply the values in column B from row 2 to row 5 by the values in column C from row 2 to row 5. Then press ‘Build’. Formula Builder will automatically generate the formula for you.

June 3, 2025 - Learn how to use ARRAYFORMULA to automate calculations, format dates, apply conditional logic, and handle dynamic data effortlessly.

February 3, 2021 - This means that the ARRAYFORMULA function is a wrapper function. You can wrap your formulas with it to apply it to a whole range of cells. Applying formulas across large data ranges. ... In order to work with it, you need to change the single cell references in the original function. Instead of cell references, you would have to pass column or row references. As an example, let’s see how can you use an ARRAYFORMULA to copy a formula down an entire column.

February 10, 2025 - It is not necessary to press Ctrl+Shift+Enter in Google Sheets, but if you do, ARRAYFORMULA( is added to the beginning of the formula. Many functions allow you use array constants like {1,2,6,12} as arguments within formulas. An example that I often use in my yearly calendar templates returns the weekday abbreviation for a given date.

The formula first multiplies the shares (cells B2 – F2) by their prices (cells B3 – F3), and then adds those results to create a grand total of 35,525. This is an example of a single-cell array formula because the formula lives in just one cell.

January 29, 2024 - The simplest way to use ArrayFormula is to apply a basic arithmetic operation to a range of cells. For example, if you wanted to multiply each value in column A by 2, you could use the following formula: =ARRAYFORMULA(A1:A10*2)

To do this, enter the ARRAYFORMULA function then A2+B2 and replace the cell references with the entire ranges A2:A9+B2:B9. ... The formula (entered only in cell C2) will then return the result of the addition for each pair of numbers: In the following example, the IF function checks if the quantity has been entered and then calculates the total amount (this example is a copy of that from the IF function):

November 19, 2024 - If you have a very long table, ... to the bottom of the spreadsheet. The formula in cell E2 is =ARRAYFORMULA(IF(D2:D13="North",(B2:B13)*(C2:C13),0))....