CODEWITHSUNDEEP
excelvba
Alex Seidel
Alex Seidel

Reputation: 3

Using cell properties while using sumif() in excel

Is it possible to use properties of the cells being summed in the sumif() criteria?

For example, if the values 1, 2, 4, 7, 5 were in cells A1-A5, respectively, would it be possible to sum up only the numbers equal to their respective rows? In that case, only 1, 2, and 5 would be summed to get 8.

Thank you!

Upvotes: 0

Views: 170

Answers (2)

teylyn
teylyn

Reputation: 35915

You can use an array formula along these lines and confirm with Ctrl-Shift-Enter:

=SUM(IF(A1:A10=ROW(A1:A10),A1:A10))

Or, as a non-array formula that can be confirmed with just the Enter key, use

=SUMPRODUCT(A1:A10,--(A1:A10=ROW(A1:A10)))

Upvotes: 1

grovesNL
grovesNL

Reputation: 6075

While array formulas can be used to do this, it's much easier to use a helper column for advanced cases.

For example, in cells B1-B5 you could write a formula that checks the whether the value is equal to the row. For cell B1:

=IF(A1=ROW(),A1,0)

Then drag this down to fill B1-B5. Then you can simply sum these values instead.

Alternatively, if you are using SUMIFS with multiple conditions, you may use =A1=ROW() for cell B1 and use the column B as one of the conditions. Then the sum could be =SUMIFS(A:A,B:B,TRUE)

Upvotes: 0

Related Questions