sumproduct with different array sizes

[sccheng888]

hi, I am trying to figure out a way of using sumproduct with mulitple criteria to return the sum of values within a set of columns. Attached is the example.

Column A = contains values for criteria 1
column B = contains values for criteria 2
columns C - H inclusive = contains values for months 1 - 6



based upon a particular month (range name "include"), i want to be able to get the YTD results based upon the criterias i set.

i have been trying

=SUMPRODUCT(--(A2:A10="A"),--(B2:B10=6),OFFSET(C2,0,0,,include))

and
=SUMPRODUCT(--(A2:A10="A"),--(B2:B10=6),sum(OFFSET(C2,0,0,,include)))

but it seems i keep running into issues with the different array size in columns C-H. i guess i need to somehow get it down to one column some how so that sumproduct can do its magic.

any ideas?Test.xlsx

[jason.b75]

Not sure if I follow exactly what you want, see if this works.

Please Login or Register  to view this content.

Result shows 0 on your test because no row meets the criteria of "A" and 6.

[Tony Valko]

Here's another one...

=SUMPRODUCT((A2:A10="A")*(B2:B10=6)*C2:INDEX(C2:H10,0,include))

The result is currently 0 as the criteria in columns A and B are not met.

[sccheng888]

that did it....i don't understand why it work with * but not with --, ....need to read up on this one. thanks

[sccheng888]

thanks. seems to be the big difference is using the * instead of --,...need to read about this.

[FDibbins]

Or this 1 (just to fix your formula)...
=SUMPRODUCT((A2:A10="A")*(B2:B10=6)*OFFSET(C2,0,0,9,include))

[jason.b75]

Or this 1 (just to fix your formula)...

Already done that way back in post #2.

[FDibbins]

Already done that way back in post #2.

Not quite the same.
Yours...
=SUMPRODUCT((A2:A10="A")*(B2:B10=6)*OFFSET(C2:C10,0,0,,include))
Mine...
=SUMPRODUCT((A2:A10="A")*(B2:B10=6)*OFFSET(C2,0,0,9,include))


You wont necessarily know to go down to C10, but the 9 can be calc'd with something like this, to make it more dynamic...
=SUMPRODUCT((A2:A10="A")*(B2:B10=6)*OFFSET(C2,0,0,COUNTA(A:A)-1,include))

[jason.b75]

I was wondering if you read mine as

=SUMPRODUCT((A2:A10="A")*(B2:B10=6)*OFFSET(C2,0,0,,include))

and thought I had only fixed part of it.

[FDibbins]

In excel, there are often many many ways of doing the same thing, thats what makes it so interesting
Thanks for the feedback

[Tony Valko]

thanks. seems to be the big difference is using the * instead of --,...need to read about this.

See this...

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