CODEWITHSUNDEEP
exceloffsetsumproduct
Ryan S
Ryan S

Reputation: 55

SumProduct, Left, and Offset sumrange by -1 Row, 1 column

I am trying to create a formula that will search column A for West* (aka LEFT(A:A,4)="West" ) and sum the corresponding number that is stored one row up and one column right.

Other criteria:

This works if the data is stored in the same row as "West"; I've played around with an offset formula in the sumrange to no avail

SUMPRODUCT(--(LEFT(A3:A100,4)="West"),--(B3:B100))

I've also tried: SUMPRODUCT(--(LEFT(A:A,4)="West"),(B:B-1)) based on SUMPRODUCT to get row +1

Data Example

Upvotes: 0

Views: 415

Answers (1)

BigBen
BigBen

Reputation: 50162

No need to OFFSET, just use an offset range, i.e. shift the 2nd range up one row to B2:B99.

SUMPRODUCT(--(LEFT(A3:A100,4)="West"),--(B2:B99))

Upvotes: 3

Related Questions