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) *........(sheet1col6)) I only get a zero '0' back.

When I use:
SUMPRODUCT(--(sheet1!col1=sheet2$bw$9)--(sheet1!col2=sheet2$A11)--........(sheet1col6))
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.

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.

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.

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.

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.

Hi

If you want to mail me a copy of the sheet direct, I will take a look to
see if I can spot what is wrong.
Omit NOSPAM from my address to mail direct.

--
Regards

Roger Govier


"sahafi" wrote in message
...
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.

Here's the formula:
=SUMPRODUCT(--(Cuts!$A$2:$A$22444=Model!$C$8)*(Cuts!$B$2:$B$2244 4=Model!$A11)*(Cuts!$C$2:$C$22444=Model!$B11)*(Cut s!$H$2:$H$22444=Model!$C$9)*(Cuts!$F$2:$F$22444)).
Columns: A, B, C, and H are formatted as 'text' while Column F is number,
and they match their corresponding cells in the 'Model' sheet. The data in
the 'Cuts' sheet has been imported into Excel from Business Objects. The same
formula is working on data that has been imported from MS Access, but not on
this. Currently i'm getting '#N/A' even though there's data the matches the
criteria above, but the formula can't get it.

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


"Roger Govier" wrote:

Hi

If you want to mail me a copy of the sheet direct, I will take a look to
see if I can spot what is wrong.
Omit NOSPAM from my address to mail direct.

--
Regards

Roger Govier


"sahafi" wrote in message
...
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.

Hi

Try breaking the formula down into the 5 constituent parts and see which
is returning the error e.g.
=SUMPRODUCT(--(Cuts!$A$2:$A$22444=Model!$C$8))

Then, if it is one of the Text ones, try doing
=LEN(cell) where cell is the cell containing data being matched, from
Model, and on one of the cells you think should match from Cuts.
You may find there are extra spaces or hidden Non-breaking spaces
Char(160) in the data that has been imported.

David McRitchie has a TRIMALL function on his site that is useful for
cleaning up data
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
--
Regards

Roger Govier


"sahafi" wrote in message
...
Here's the formula:
=SUMPRODUCT(--(Cuts!$A$2:$A$22444=Model!$C$8)*(Cuts!$B$2:$B$2244 4=Model!$A11)*(Cuts!$C$2:$C$22444=Model!$B11)*(Cut s!$H$2:$H$22444=Model!$C$9)*(Cuts!$F$2:$F$22444)).
Columns: A, B, C, and H are formatted as 'text' while Column F is
number,
and they match their corresponding cells in the 'Model' sheet. The
data in
the 'Cuts' sheet has been imported into Excel from Business Objects.
The same
formula is working on data that has been imported from MS Access, but
not on
this. Currently i'm getting '#N/A' even though there's data the
matches the
criteria above, but the formula can't get it.

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


"Roger Govier" wrote:

Hi

If you want to mail me a copy of the sheet direct, I will take a look
to
see if I can spot what is wrong.
Omit NOSPAM from my address to mail direct.

--
Regards

Roger Govier


"sahafi" wrote in message
...
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.