CountIF and OFFSET

[Steve Jackson]

I am trying to create a 2 part formula. The first bit, I need the
formula to find the occurances of a word within a range of cells (e.g.
F3:J10).

That is straight forward using the =COUNTIF(Range,Reference) formula
and brings back the correct result. However the 2nd part is causing me
trouble, where I am trying to do this: For every time the word occurs
I need the formula to count the number in the cell that is one column
to the right.

e.g.
Range of cells is "F3:K10"
Specified word is "Cabbage"
Cabbage appears in cells F5, H8 and J9
The numbers in Cells G5, I8 and K9 are 1, 2 and 1
The formula should bring back the answer 4 (i.e.1+2+1)
All other cells in the range are scanned but ignored because the word
cabbage does not appear.

I need the formula to say, yes I see where Cabbage appears and I will
count the numbers that are in the cells that are 1 column to the
right.

I have tried using OFFSET function within the COUNTIF function but it
brings back the wrong answer. Is it best to use these combined
functions?If so, how?

Any help would be much appreciated.

Steve

 

[Frank Kabel]

Hi
=SUMIF(Range,Reference,Sum_Range)

 

[Peo Sjoblom]

Use SUMIF

=SUMIF(Range1,"Cabbage",Range2)

Regards,

Peo Sjoblom

 

[Steve Jackson]

Thank you both for pointing me in the direction of the right function to
use. For the Range2 element I inserted the OFFSET function to give:

=SUMIF(Range,Reference,OFFSET(Range,0,1))

e.g. =SUMIF(A1:Z99,Cabbage,OFFSET(A1:Z99,0,1))

I have several corresponding columns in a block of cells and it comes up
with the correct answer!

 

[RagDyer]

Another way:

=SUMPRODUCT(--(A1:Y99="Cabbage"),B1:Z99)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Use SUMIF

=SUMIF(Range1,"Cabbage",Range2)

Regards,

Peo Sjoblom