Thanks for trying.

I went through the formula with the fine-tooth comb, letter by letter, and
digit by digit. All look good. It's most likely has to do with the data, but
I have checked it more than 6 times.
May be someone who has gone through a similar situation can shed some light
on how to tackle this.

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


"Bob Phillips" wrote:

Well my experience with SP is that if the answer is wrong, you either made a
mistake in the formula, or the data is wrong. There is insufficient
information to elaborate any further.

--
HTH

Bob Phillips

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

"sahafi" wrote in message
...
Yes, I'm aware of that. Actually all my ranges are from: A2:A20500,
B2:B20500, etc. Just for the sake of typing I included col1, col2, etc. To
avoid making mistake, I usually click on the sheet tab that I would like
to
get the data from, so all of them do have '!' after the name. Like I said
the
formula does return value, just not the correct value.
--
when u change the way u look @ things, the things u look at change.


"Bob Phillips" wrote:

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.