sumproduct failure?

[Herman56]

I have two sheets: pricelist and products. In products an "x" should
appear when in pricelist an "x" is added in the referenced packing
column. In both sheets the product names are to be found in column B.
These packings are:

C=125 ml
D=250 ml
E=500 ml
F=500 ml+
G=1,0 liter
H=2,5 liter
I=5,0 liter
J=10,0 liter
K=0,75 kg
L=1,0 kg

In pricelist as well as in products there are empty spaces in each
productline, but since there is no tag ("x") in the concerning column,
there does not appear an "x" in the product column. Only when the
packing is 1,0 liter, the formula suddenly decides that both conditions
are true, where there is absolutely no "x" on empty spaces nowhere in
the pricelist-sheet...

My formula used is this one:


=IF(SUMPRODUCT(--(pricelist!$G$3:$G$250="x")*--(pricelist!$B$3:$B$250=$B3))<>0;"x";"")

Ofcourse I can rule out all the blank spaces from being checked, by
adding IF(SUM($B3)="";"" to the formula, but I want to know why my
perfectly working formula is not working on the 1,0 liter column...
Anyone please?

Paul

 

[Bob Phillips]

Herman,

It works fine for me (as I understand it), but I am in the UK and use the
comma delimiter, not semi-colon, and wonder if there is anything about that.

Can you try this array formula and see if this also suffers?

=IF(ISNUMBER(MATCH(1,(pricelist!$G$3:$G$250="x")*(pricelist!$B$3:$B$250=$B3)
,0)),"x","")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Herman56]

Hi Bob

Thanks for your reply! Your IF/ISNUMBER/MATCH-formula got rid of th
disturbing x's in the 1,0 liter column, but got rid of all other x's a
well... :-( :-
Since that is even further from home, I changed back to my origina
formula... :-

I did check the cell formats, but they are simular in each colum
concerned in the products sheet and in the pricelist columns. I fail t
see any difference between f.i. the 1,0 liter column and the 0,5 lite
or the 10,0 liter column. Yet it is the only column affected by thi
peculiar behaviour..

I tried clearing the cells, but this did not make a difference a
all... I still am puzzled why this is affecting only the 1,0 lite
column... I have changed all the formulas by adding th
IF($B3="";"";-part to it, and all columns are behaving correct now..
But it should not have been necessary at all... :-

Changing back the formulas did make an "x" pop up immediately at th
rows without entries... When you have another idea about it's cause an
it's solution, I would love to hear it, Bob! From anyone, btw... :-

Pau

 

[Herman56]

Hi Bob!

Thanks for your reply! Your IF/ISNUMBER/MATCH-formula got rid of th
disturbing x's in the 1,0 liter column, but got rid of all other x's a
well... :-(
Since that is even further from home, I changed back to my origina
formula...

I did check the cell formats, but they are simular in each colum
concerned in the products sheet and in the pricelist columns. I fail t
see any difference between f.i. the 1,0 liter column and the 0,5 lite
or the 10,0 liter column. Yet it is the only column affected by thi
peculiar behaviour...

I tried clearing the cells, but this did not make a difference a
all... I still am puzzled why this is affecting only the 1,0 lite
column... I have changed all the formulas by adding th
IF($B3="";"";-part to it, and all columns are behaving correct now..
But it should not have been necessary at all... :-(

Changing back the formulas did make an "x" pop up immediately at th
rows without entries... When you have another idea about it's cause an
it's solution, I would love to hear it, Bob! From anyone, btw... :-D

Pau

 

[Bob Phillips]

Send me the spreadsheet?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)