Google Sheets intermediate arrayformula sumif dynamic-calculation

How to Use ARRAYFORMULA with SUMIF in Google Sheets?

SUMIF with ARRAYFORMULA is a Google Sheets function that calculates the sum of values in a specified range based on criteria applied to another range for all rows at once.. Formula Genius generates and validates this formula automatically from a plain-English prompt.

This guide shows you how to apply SUMIF across multiple rows simultaneously, ensuring your calculations adjust as data changes.

Generate This Formula Browse Library

The Formula

Prompt

"Create a SUMIF that applies to every row simultaneously using ARRAYFORMULA, updating automatically when new rows are added"

Google Sheets 
=ARRAYFORMULA(SUMIF(A:A, C:C, B:B))

This formula calculates the sum of values in a specified range based on criteria applied to another range for all rows at once.

Step-by-Step Breakdown

  1. ARRAYFORMULA allows the formula to process multiple rows simultaneously.
  2. SUMIF checks each value in range A:A against the criteria in C:C.
  3. If a match is found, it sums the corresponding values from range B:B.
  4. This setup automatically updates as new rows are added to the data.

Edge Cases & Warnings

  • The formula may return incorrect results if there are blank cells in the criteria range.
  • If the ranges A:A, B:B, and C:C are not of equal length, it may lead to errors.
  • Using non-numeric values in the sum range can cause the formula to return unexpected results.

Examples

Prompt

"Criteria in C:C is 'Apple', values in B:B are 10, 20, 30, and A:A has corresponding fruits."

Google Sheets 
The formula returns 30 for 'Apple'.
Prompt

"Criteria in C:C is 'Banana', values in B:B are 5, 15, and A:A has corresponding fruits."

Google Sheets 
The formula returns 15 for 'Banana'.

Frequently Asked Questions

Can I use multiple criteria with this formula?

No, SUMIF only allows for a single criterion; use SUMIFS for multiple.

What happens if I add new rows?

The formula will automatically include new rows in the calculation.

Can I use this with non-contiguous ranges?

No, the ranges must be contiguous for ARRAYFORMULA to work correctly.

Can't find what you need?

Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.

Generate Custom Formula See Examples