CODEWITHSUNDEEP
excelexcel-2007excel-2010vba
Adam
Adam

Reputation: 777

Sum a range of column

I am kind of new to excel, could you please help me with below question?

I want to a sum of column only if the all the column in the row have value, could you please help me on this?

   A B C D E F

 1 1 2 3 4 5 10

 2 6 4 5 6 7 9

 3 8   5 6 7 10

 4 9    3 3  3

I want to do a sum of f1:f100 and I want to do sum only if A B C D E have values in it, if any of the column in a row have empty value, i want to ignore it. like now I need to ignore F3 and F4.

Basil Abraham.

Upvotes: 0

Views: 673

Answers (2)

barry houdini
barry houdini

Reputation: 46331

You can use SUMIFS like this

=SUMIFS(F1:F100,A1:A100,"<>",B1:B100,"<>",C1:C100,"<>",D1:D100,"<>",E1:E100,"<>")

or this version is less transparent but more easily "extensible" if you want to check more than 5 columns

=SUMPRODUCT((COUNTBLANK(OFFSET(A1:E1,ROW(A1:A100)-ROW(A1),0))=0)+0,F1:F100)

Upvotes: 1

Nik Drosakis
Nik Drosakis

Reputation: 2348

select the rows that you want to sum and use the S button, empty rows do not care you.

Upvotes: 0

Related Questions