CODEWITHSUNDEEP
excelexcel-formulasumarray-formulassumifs
Henry
Henry

Reputation: 11

How to use SUMIF in an array formula to not sum blank rows

Hi I am using the following array formula to sum rows AC-AG , I would like to modify it so that it does not sum if an entire row is blank. How would I do this using an array formula since this spreadsheet is tied to a google forum and gets updates often.

I am using this formula at the top of the column.

={"# Boxes of Household Items"; ARRAYFORMULA(SUMIF(IF(COLUMN(AC1:AG1),ROW(AC2:AG)),ROW(AC2:AG),AC2:AG))}

Example Below

The Formula adds up AC through AG and puts them in AR. I want the formula to not put a zero in AR5 since the entire row is blank

Example

Upvotes: 1

Views: 816

Answers (1)

Robson
Robson

Reputation: 2032

enter image description here

In the image above I have numbers in various cells from columns A:E, then I have a formula in cells F1:F3.

  • Row 1 has numbers for all cells.
  • Row 2 has numbers for some cells.
  • Row 3 has no numbers.

To show the sum when all/some of the cells contain numbers, I would do:

=IF(COUNT(A1:E1)>0,SUM(A1:E1),"")

We check IF there is at least one number using COUNT(A1:E1)>0. If there is at least one number, we then show the sum using SUM(A1:E1) otherwise we just show nothing, which is the "" part.

For you the formula would be this in AR2:

=IF(COUNT(AC2:AG2)>0,SUM(AC2:AG2),"")

Upvotes: 1

Related Questions