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!
"Peo Sjoblom" <(E-Mail Removed)> wrote in message
news:1D903655-03B2-4C7E-A9CC-(E-Mail Removed)...
> Use SUMIF
>
> =SUMIF(Range1,"Cabbage",Range2)
>
> Regards,
>
> Peo Sjoblom
>
>
> "Steve Jackson" wrote:
>
> > 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
> >
|
Similar Threads
