Hi Bernard,

This looks promising, but I think I need a bit more hand holding. Example 4

looks similar, but I don't see the whole picture. I'll expand on my concept

and see if that changes the answer. The cell layout is:

J K L O P Q T U V

31 one text 5 two text 7 four text 9

32 two text 6 three text 8 two text 10

33

34

C74 is the value I'm matching if it = "two"

then sumif returns the sum of L32, Q31, V32.

Will the SUMPRODUCT be able to do this?

Thanks,

John

"Bernard Liengme" wrote:

> For details on SUMPRODUCT

> Bob Phillips

> http://www.xldynamic.com/source/xld.SUMPRODUCT.html

> J.E McGimpsey

> http://mcgimpsey.com/excel/formulae/doubleneg.html

>

> --

> Bernard V Liengme

> Microsoft Excel MVP

> http://people.stfx.ca/bliengme

> remove caps from email

>

> "DocBrown" <(E-Mail Removed)> wrote in message

> news:(E-Mail Removed)...

> > Hello,

> >

> > I have a worksheet where I'm trying to use the sumif function to sum over

> > multiple criteria ranges and multiple sum_ranges. The following formula,

> > which shows three ranges, (surprisingly) works. Is there a more compact

> > way

> > to implement this? This is for a budget spreadsheet where each of the

> > ranges

> > (J31:J36 for example, are one month. So the full scale formula would have

> > 12

> > ranges and sum_ranges. Notice that the sum_ranges are offset two columns

> > right of the critera range.

> >

> > =SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="&C47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36))

> >

> > If I rearranged the columns, would it that make it possible for a more

> > compact formula to work?

> >

> > Thanks,

> > John

> >

>

>

>