Sumproduct Formula Not Behaving Properly

[heuettm]

I have a sumproduct formula that I used for a similar spreadsheet on a different computer, but when I tried inputting into a new spreadsheet on my current home computer it didn't work at all (both times I was using Excel 2000, as it's the only version of the program I own):

=SUMPRODUCT(--($E$2:$E$1000="Player Name"),--($B$2:$B$1000<>"All"),$G$2:$G$1000)

I came up with this particular one through trial and error (I mostly use Excel for very simple tables -- as it turns out, analyzing football statistics doesn't take a lot of complicated equations). For this spreadsheet, the E column contains the players' name, the G column contains the stat I want to total, and the B column contains a number designation for the individual games in which the stats were earned. This formula is supposed to find all the rows containing the player's name and total up the numbers in the G column for every game. Theoretically, I am avoiding a circular reference by telling it to ignore the row in which the formula has been entered by entering the word "All" in the B column instead of a number.

However, instead of displaying the correct result all Excel gives me is a big fat zero. So, where did I go wrong? Did I screw up the formula somehow? Is there another way I should go about doing this?

[ajryan88]

Hi,

Your formula looks fine, could you perhaps upload your workbook for me to take a look at what might be going wrong?

An alternative formula would be a SUMIFS:

Please Login or Register  to view this content.

Let me know if this helps

EDIT: Ignore the suggested SUMIFS formula, it doesn't apply to Excel 2000

[:) Sixthsense :)]

The formula is fine... Just confirm us where you are entering the current formula?

[FDibbins]

Hi and welcome to the forum

Perhaps another way around this would be to use a helper column to combine name and game number, and then using sumif() based on that helper?

[Jonmo1]

Theoretically, I am avoiding a circular reference by telling it to ignore the row in which the formula has been entered by entering the word "All" in the B column instead of a number.

No, that's not right...

If you're entering the formula in any cell that the formula references, then it's a circular reference.
Regardless if the cell you entered it in is on a row that doesn't meet the criteria.
It's still a circular reference, because it still must evaluate that cell if it meets the criteria.

[heuettm]

Hi,

Your formula looks fine, could you perhaps upload your workbook for me to take a look at what might be going wrong?

An alternative formula would be a SUMIFS:

Please Login or Register  to view this content.

Let me know if this helps

EDIT: Ignore the suggested SUMIFS formula, it doesn't apply to Excel 2000

Okay, will do -- "Example" contains the relevant sheet of the new workbook, and "Example 2" is from the earlier workbook in which the formula appears to be working (it appears to be giving me a circular reference error now, however). The statistic in this instance is just a simple passing targets versus catches comparison broken down by different areas of the field.

[ajryan88]

Hi,

The reason you are getting a circular reference, as Jonmo1 correctly stated, is because you are entering your formula in cell G3, and the SUMPRODUCT range includes G3.

This is why you are getting 0. If your formula was somewhere else that wasn't creating a circular reference, say H3 instead, then the value returned would be 3, as in the case of your example.

Hope this helps

[FDibbins]

the G column contains the stat I want to total,

That sounds like you want to total INTO that column, based on data in other columns? You cannot run a count - or sum - on data and put that calc inside the range you are summing - or counting

I would suggest that you create a 2nd table, and then base that on the stats from the 1st table?

[heuettm]

That sounds like you want to total INTO that column, based on data in other columns? You cannot run a count - or sum - on data and put that calc inside the range you are summing - or counting

I would suggest that you create a 2nd table, and then base that on the stats from the 1st table?

Yeah, that's probably the simplest solution here. I was hoping there was a way to massage the formula so that I could keep it in the same column and on the same sheet, and I thought I'd done just that with my previous workbook. The circular reference error aside, do you have a guess as to why the formula is working at all in the example 2 workbook?

[FDibbins]

circ reff errors show up in really unexpected ways/places. I have seen a circ error ref to a cell that was in no way involved, bit was related to another cell 4 calcs "deeper".

Often the only way ti find where the error lays, is to start deleting cells that it says are at fault - then "undoing" if there is no change