Offset Count if, between two words!

[harteorama]

Hi all,

Does anybody know how to do an offset countif, between two words in
vba?

I want to count the number of +1 or -1's, but, there can be varying
amounts of +1's & -1's between these two Words.

The words (apple and orange) are always in Col A, and the 1's are
always in Col C.

Heres what i have in my file....

Col A Col B Col C Col D
APPLE
1
-1
-1
1
ORANGE

This would give me in Cell C1 = Count of number of +1's
Cell D1 = Count of number of -1's


Any pointers would be greatly appreciated..


Many thanks

PJ

 

[Bob Phillips]

You can use formulae

C1:
=SUMIF(INDEX(C1:C1001,MIN(IF(A1:A1001="APPLE",ROW(A1:A1001)))):INDEX(C1:C100
1,MIN(IF(A1:A1001="ORANGE",ROW(A1:A1001)))),1)

D1:
=SUMIF(INDEX(C1:C1001,MIN(IF(A1:A1001="APPLE",ROW(A1:A1001)))):INDEX(C1:C100
1,MIN(IF(A1:A1001="ORANGE",ROW(A1:A1001)))),-1)

these are array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[harteorama]

Hi Bob,

Many thanks, i did consider using arrays formulas, but the data in the
cells is copied via a macro i have into this sheet, so i dont want to
keep doing the whole CTRL+ALT+Enter thing.. every time i do an update.
The update removes all previous data and adds new stuff....

Thats why i was looking for a vba way to do this.. i.e. run the macro
and 'stamp' the result into a cell.

Many many thanks for your help.

PJ