Too many variables on sumproduct?

**nataliarizzatti** · 10-31-2011, 06:04 PM

Hi,

My sumproduct is working for the first 4 array although the it is not "filtering" for the 5th variable.

Here is the formula:

=SUMPRODUCT(--(ext_color=Availability!C16),--(ext_gauge=IF(ISNUMBER(selected_gauge),selected_gauge,ext_gauge)),--(ext_width=IF(ISNUMBER(selected_width),selected_width,ext_width)),--(ext_finish=IF(ISNUMBER(selected_finish),selected_finish,ext_finish)),--(ext_embossing=IF(ISNUMBER(selected_embossing),selected_embossing,ext_embossing)),ext_coil)

ps. not working for embossing.

Any ideas?

Thanks

**nataliarizzatti** · 10-31-2011, 06:12 PM

I did some research and I think here is the problem

SUMPRODUCT treats array entries that are not numeric as if they were zeros

My embossing is a letter. All other conditions are numbers.

Do you know what it the solution for that. I can't change all the data base just to make it work.

Thanks

**nataliarizzatti** · 10-31-2011, 06:27 PM

I fixed it.

I was supposed to be asking "istext" instead of "isnumber"

Tks

**ConneXionLost** · 10-31-2011, 06:45 PM

If you have your solution, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save