Guys,

I am having trouble with SUMPRODUCT function. Its working in one worksheet and not in other.

I have tried everything but cant find out the reason.

In the attached worksheet, the function works in "example" worksheet and not in "MSF" worksheet. The function is in Yellow cell.

If anyone of you can find the error, I will appreciate it!

Thanks!

I hadn't seen the SUMPRODUCT function before. It is pretty useful in the right circumstances! It works a lot like the SUMIF function, but you can use more than one condition.

I think the problem comes with how Excel handles numbers. After playing a little, it appears that a condition like a1="8" only works when the value of cell a1 is treated as a text string, not a number. Curiously, even using the FORMAT/CELL/TEXT menu doesn't seem to do the trick.

If you go to the data and type a single quote before each week number (e.g. '27 ), you can force Excel to treat the characters as text. If you do this to all the appropriate data, then I think it will work. Kind of a pain to have to retype the data, though. :-(


Tim F

P.S. Another solution. You could add add a column and use the TEXT function to convert th number to text and then use that column for the SUMPRODUCT testing. I think that will work! You could even hide the column to keep it looking neat.

Guys,

I am having trouble with SUMPRODUCT function. Its working in one worksheet and not in other.

I have tried everything but cant find out the reason.

In the attached worksheet, the function works in "example" worksheet and not in "MSF" worksheet. The function is in Yellow cell.

If anyone of you can find the error, I will appreciate it!

Thanks!

Hi!

I am not familiar with the SUMPRODUCT Function,
but i found out that the function is not reading
a # value under " " sign but works for the text value.

I did a quick fix of your program.
please see attached.

I also inserted another column for the particular length,
and refer it to the function for ease.
The modified original function is still shown for comparison.

I hope thats what you need.

Hi!

I am not familiar with the SUMPRODUCT Function,
but i found out that the function is not reading
a # value under " " sign but works for the text value.

I did a quick fix of your program.
please see attached.

I also inserted another column for the particular length,
and refer it to the function for ease.
The modified original function is still shown for comparison.

I hope thats what you need.
----

was anybody able to open the file? Will appreciate if you can reattach the file!

thanks!

----

was anybody able to open the file? Will appreciate if you can reattach the file!

thanks!

I was able to open the file with no problems.

I was able to open the file with no problems.

Would you mind reattaching the file. Thanks for your time!

Would you mind reattaching the file. Thanks for your time!

Maybe you can open this one. I have saved and reposting with this thread.

I think the problem comes with how Excel handles numbers. After playing a little, it appears that a condition like a1="8" only works when the value of cell a1 is treated as a text string, not a number. Curiously, even using the FORMAT/CELL/TEXT menu doesn't seem to do the trick.



In cases like that (and you run into this with trying to convince Excel that dates are dates sometimes) is to highlight the column involved, go to the Data menu, select "text to columns". Go through that is if you are parseing the column (just select delimited) and on step 3 select Text as the option (or date if you are having trouble with dates).

Frm \exceltips.com. Your equation will also work if the quotes are removed.

Frm \exceltips.com



Sam, Sam....welcome back, missed your posts. Hope you are doing OK:bigwave:

Sam, Sam....welcome back, missed your posts. Hope you are doing OK:bigwave:

Yeah - me too! Hope all is well.:bigwave:

Thanks Cari, Thanks Ralph, I missed the action.:D