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

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)

wrote in message
ups.com...
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

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