Creating a dynamic sum formula in Google Sheets

Other Questions Google Sheets
1

I’m trying to make a formula that adds up cells in a row when a condition is met. Right now I have this:

=IF(A1="online", SUM(B1,C1,D1,E1,F1), B1)

But I want it to work for all rows automatically. I tried using ArrayFormula like this:

=ArrayFormula(IF(A1:A="online", SUM(B1:B, C1:C, D1:D, E1:E, F1:F), B1:B))

The problem is it’s adding up all the cells in those columns instead of just the ones in each row. How can I fix this so that it works correctly row by row? Any help would be appreciated!

2

I’ve encountered this issue before, and there’s a neat trick using SUMIF that can solve it. Try this formula:

=ArrayFormula(IF(A1:A=“online”, SUMIF(ROW(B1:F1), ROW(B1:F), B1:F), B1:B))

This checks each row in column A for “online”. If true, it sums B to F for that specific row. Otherwise, it returns the value from B.

The key is using ROW(B1:F1) in SUMIF to ensure it only sums within each row. This should work across all rows automatically.

Test it out and see if it meets your needs. If you run into any issues, feel free to ask for more clarification.

3

I’ve dealt with a similar issue before, and I found a solution that might work for you. Instead of using SUM, try using SUMIF with a range. Here’s an example:

=ArrayFormula(IF(A1:A=“online”, SUMIF(ROW(B1:F1),ROW(B1:F1),B1:F), B1:B))

This formula checks if the value in column A is “online”, and if so, it sums up the values in columns B through F for that specific row. If it’s not “online”, it just returns the value from column B.

The trick is using ROW(B1:F1) as both the criteria and sum range in SUMIF. This ensures that only values from the same row are summed.

Give it a try and see if it solves your problem. Let me know if you need any clarification on how it works.

4

hey grace, have you tried using the SUM function with a range? somthing like this might work:

=ArrayFormula(IF(A1:A=“online”, SUM(B1:F1), B1:B))

it should sum up B to F for each row where A is “online”. let me know if that helps!