SumProduct formula stopped working

[Amanda McRoberts]

Good morning,

I created the below worksheet earlier this year to assist with billing a customer more efficiently. Initially the worksheet pulled the necessary information using a VLOOKUP() formula, and after going through Chandoo's Excel school the SUMPRODUCT() wound up being more efficient. Initially the formula was working, however when we went into the workbook today, the entire workbook ended up pulling #REF error. I can't seem to find a reason for the formula to suddenly break.

This also occurred in another workbook, and I just went back to using the VLOOKUP() formula for that workbook and finalizing the cells since the tanks get reused.

 

[Chihiro]

Change this portion where you reference [#Headers]
(AgilityTanks[#Headers]=Chongqing[[#Headers],[Tip Date]]),AgilityTanks)

To
AgilityTanks[Tip Date]

So for Tip Date (in Chongqing sheet)
=SUMPRODUCT((AgilityTanks[Column1]=[@Tank])*(AgilityTanks[M&S Ref]=[@[M&S Ref]])*AgilityTanks[Tip Date])

 

[KenU]

Amanda,

It looks like the #REF! errors originate on the Agility Tanks worksheet in F95:F102. Deleting those entries (which appear to be pasted or written by code and not the result of in-cell formula errors) corrects the errors reported on the other two sheets.

Hope that helps.
Ken

 

[Luke M]

Tagging on to what Chihiro said, need to correct formula in D2 and in G2. Same problem in each, bad way of referencing the column in AgilityTanks.

=SUMPRODUCT((AgilityTanks[Column1]=[@Tank])*(AgilityTanks[M&S Ref]=[@[M&S Ref]])*AgilityTanks[Ata])
and
=SUMPRODUCT((AgilityTanks[Column1]=[@Tank])*(AgilityTanks[M&S Ref]=[@[M&S Ref]])*AgilityTanks[Tip Date])

 

[Amanda McRoberts]

Thank you! Now I can show the solution to my co-workers to use in future worksheets too.

 

[jeffreyweir]

BY the way, SUMIFS is many, many more times efficient than SUMPRODUCT. Never use SUMPRODUCT if you can do the same thing with SUMIFS, is my golden rule. In fact I devote a whole chapter to this in the book I'm working on.

 

[Chihiro]

Yep. Most of the time I use it to be compatible with legacy Excel 2003 users.

 

[jeffreyweir]

Good point. Another of my general rules is never use a SUMPRODUCT or SUMIFS etc if a PivotTable will do.