Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

And all sheets are terminated by a ! to show it is a sheet name

SUMPRODUCT(--(sheet1!A1:A1000=sheet2!$bw$9)*(sheet1!b1:B1000=sh eet2!$A11)*..
.......(sheet1!F1:F1000))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"sahafi" wrote in message
...
Hi,

I'm using sumproduct and also index, match formulas/function with correct
result in some area of my workbook, but with incorrect result in another
area
of the workbook.
When I use:

SUMPRODUCT(--(sheet1!col1=sheet2$bw$9)*(sheet1!col2=sheet2$A11) *........(she
et1col6)) I only get a zero '0' back.

When I use:

SUMPRODUCT(--(sheet1!col1=sheet2$bw$9)--(sheet1!col2=sheet2$A11)--........(s
heet1col6))
I get the total for the entire column (col6) instead of the subtotal that
matches those 4 criteria.
I also tried INDEX(sheet1col6), MATCH(................)) But this one grab
only the value on the first row instead of summing up 10 or more rows.

All 4 columns on sheet1 are formatted as text (imported from Access),
while
col6 as number and they match same format on sheet2. Like I said, both
formulas working fine on different data, but not here. I just couldn't
figure
it out. I have tried copying a blank cell and do paste special/add but it
didn't help either. Any direction on this is very much appreciated.

Thanks.
--
when u change the way u look @ things, the things u look at change.