CODEWITHSUNDEEP
arraysexcelif-statementsumifs
Adam Keith
Adam Keith

Reputation: 37

Excel - Perform Summation of Column of If Statements In One Cell

I feel like I've done this before but am at a total loss after looking at a lot of pages already.

Consider a set of data given in two columns, x and y. X column is always ascending in value, y is random. I need a single cell to calculate the sum of a column of IF statements within a specified range of x without actually displaying the column of IF statements or using VBA. In other words: I want to turn this (IMG1) into this (IMG2)

In the first picture, the z column has this formula for each cell in a descending order: =IF(AND(B2>0,A2>$D$2,A2<$D$3),A2-A1,0)

I feel like I can use SUMIFS or an array formula somehow but I'm at a loss.

Please forgive if I improperly posted this somehow - this is my first post.

-Adam

Upvotes: 1

Views: 43

Answers (1)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

Try this Array Formula which requires a special key stroke Ctrl+Shift+Enter instead of Enter alone.

=SUM(IFERROR((A2:A11>0)*(B2:B11>0)*(A2:A11>D2)*(A2:A11<D3)*(A2:A11-A1:A10),0))

Confirm this formula with Ctrl+Shift+Enter

Upvotes: 1

Related Questions