CODEWITHSUNDEEP
excelexcel-formula
Nick
Nick

Reputation: 47

Count # of cells in between separate values in offsetting rows

enter image description here

I'm trying to count how many orders a customer has in a month long period. I'm trying to add the invoice totals(Column H) for all occurrences between "Customer 1" and "Total for Customer 1". This formula has to be able to look up these values because their location changes each month.

Any help would be greatly appreciated, I'm quite stuck.

I should note there are many more customers above in this spreadsheet also containing matching information for invoice and accounts receivable in respective columns.

Upvotes: 1

Views: 56

Answers (3)

Nick
Nick

Reputation: 47

Ended up going with.

=ABS(MATCH("Customer 1",A:A,0)-MATCH("Total for Customer 1",A:A,0))-1

Upvotes: 1

user4039065
user4039065

Reputation:

MATCH returns the matching row number. Subtract one from the other and adjust the difference to compensate for your layout.

=match("Total for Customer 1", a:a, 0)-match("Customer 1", a:a, 0)-1

Upvotes: 0

Binarynam
Binarynam

Reputation: 154

I believe the solution can only be sketchy.

I propose you use the method COUNTA(cellstart:cellend), which return the number of non blank cells in the range provided. You could do something like : COUNTA(B106:B999999).

Yes, this is sketchy.

Upvotes: 0

Related Questions