Search code examples
exceloffsetsumproduct

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:

  • Must be able to sum numbers stored as text
  • must be able to reference a closed workbook without "breaking" (ie- no sumif(s), count(a,if(s)))
  • must work with blank rows in "sumrange" column
  • ideally no array formulas

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

Solution

  • 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))