# SUMPRODUCT resulting in 0

1. ## SUMPRODUCT resulting in 0

I posted the question a couple of days ago about a SUMPRODUCT formula (excel 2003) for multiple criteria including a range and you guys helped me out with this: =SUMPRODUCT(I9:I1000,(A9:A1000<=9.14)*(A9:A1000>=9.08)*(D9:D1000="Y"))
which worked perfectly.
BUT then I applied it to another set of data in the same worksheet :=SUMPRODUCT(U1:U1000,(A9:A1000<=12.21)*(A9:A1000>=12.15)*(Q9:Q1000="Y")) and all I get is "0" for the result. what did I do wrong?

2. ## Re: SUMPRODUCT resulting in 0

HI

Sample WorkBook plz

Punnam

3. ## Re: SUMPRODUCT resulting in 0

It works for me, after a small correction.
The ranges all must be the same size.
:=SUMPRODUCT(U1:U1000,(A9:A1000<=12.21)*(A9:A1000>=12.15)*(Q9:Q1000="Y"))
If I change that 1 to a 9, then it works just fine.

However, that would have resulted in a #Value! Error, not a 0..

If it's giving you 0, it means either
a) there are no rows that meet all 3 criteria
values in A9:A1000 that are between 12.15 and 12.21
and values in Q9:Q1000 that = Y (or y)

or
b) the numbers in U9:U1000 are NOT really numbers, they are "Numbers stored as text"

4. ## Re: SUMPRODUCT resulting in 0

Thanks for catching the typo (U1 instead of U9), but I corrected it and it I still get a 0.

I checked that there are numbers in the cells that meet all three criteria and there are.
How do numbers get stored as text?

5. ## Re: SUMPRODUCT resulting in 0

Is it a cell formatting issue?

6. ## Re: SUMPRODUCT resulting in 0

Sometimes this will convert text numbers into numeric numbers.

Select the range U9:U1000
Goto the Data tab (or Data menu)>Text to Columns
Just click Finish

7. ## Re: SUMPRODUCT resulting in 0

Originally Posted by DEER30
How do numbers get stored as text?
This can happen in a few different ways.

1. values/numbers that get pulled in from external programs (ie not excel) sometimes come in as text, especially CSV
2. any numbers that are created using LEFT/MID/RIGHT are always text that looks like a number

Another way to convert the text to a value is to see if there is a small green triangle in the top left of the cell. if there is, and you click on the cell, you will see a small yellow diamond. highlight all the "text" numbers, scroll back to the 1st cell. Then click the yellow triangle and select "convert to number"

8. ## Re: SUMPRODUCT resulting in 0

I think I figured it out! The number of cells being analyzed for each criteria was too great (9-1000). I restricted the range to only a few hundred and it seems to be working, so far. Is this a limitation of excel? Or do I have a "bad cell" in there somewhere? I really prefer to include the full column (9-1000) so that if future data entry requires the insertion of more rows, I don't have to rewrite each formula to accommodate them.

9. ## Re: SUMPRODUCT resulting in 0

No, 1000 rows isn't bad at all (if it IS, you have a really old system with very little ram and processor power).

Are you sure the formula was actually returning 0 ?
Is the formula you posted the 'whole' formula, or did you only post a part of a larger formula?

Can you post a sample workbook?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

10. ## Re: SUMPRODUCT resulting in 0

I'm using a system less than a year old with an i7 processor. I don't think it is the issue as I run video editing software with files several GB's in size with no problems. I will try to get a workbook posted as soon as I can.
Thanks

There are currently 1 users browsing this thread. (0 members and 1 guests)