SUMPRODUCT Wrong Data Type?

[Raesu]

Hello,


I'm using sumproduct to summarize some data from a larget table. The formula works great for everything EXCEPT my May data?!


=SUMPRODUCT(--(PlanRegion=$B17)*--(PlanMetric=$C17)*--(PlanMktProd={"A","B","C","D"})*Plan!H$2:H$141)


The column H is returning a #VALUE where everything else works as it should in other months. I've tried multiplying the column by 1 (to make sure its formatted as number), selected all the data and formatted as general. Copy and paste formats onto H...NOTHING works. Any ideas please?


Thanks.

 

[Luke M]

My guess is that somewhere there is an text value. What type of data is in col H? Dates, qty, cost, etc? If dates, a common thing I've seen is someone entering 2/30/12 (invalid date), or 2/07/20012 (too large). Other things that could go wrong would be that the other regions are not the same size.


Also, just a tip about SUMPRODUCT. You need to either use the double minus, OR multiply the criteria. You don't need to do both.


=SUMPRODUCT((PlanRegion=$B17)*(PlanMetric=$C17)*(PlanMktProd={"A","B","C","D"})*Plan!H$2:H$141)


or


=SUMPRODUCT(--(PlanRegion=$B17),--(PlanMetric=$C17),--(PlanMktProd={"A","B","C","D"}),Plan!H$2:H$141)

 

[Faseeh]

Hi Raesu,


It think it will work with a comma just before Plan!H$2:H$141.


Regards,

FASEEH

 

[Raesu]

Fixed...there was a blank in one of the cells for that month (as opposed to a 0). Sorry I didn't catch that before posting.


Sumproduct continues to baffle me...my formula works perfectly. Also works perfectly if I remove the -- but still multiply.


Time to read the formula forensic and advanced article you wrote Hui for the 100th time...and still only understand a fraction of it!

 

[Luke M]

Raesu,

Sorry for not being clearer in my earlier post. There was nothing wrong with the original formula, it just was extra work. Go with whatever makes sense to you. =)