Problems using the SUMPRODUCT function

[Alf]

I was reading this thread

http://www.excelforum.com/excel-form...our-block.html

where FDibbins used SUMIF to solve the OP's problem.

Curious to see if this was solvable using SUMPRODUCT I had a go at it but the result was rather disappointing. Two results correspond with what is expected and at least three results are wrong.

Unable to see what the problem is I hope some forum member may solve this.

Alf

[Ace_XL]

At first glance..shouldn't formula in I25 be

Please Login or Register  to view this content.

copied down

[Fotis1991]

Hi Alf

=SUMPRODUCT(($C$2:$C$8=C25)*($D$2:$D$8>=D25)*($D$2:$D$8<=E25)*($F$2:$F$8))

Looks to work for me.

[Alf]

Hi Ace_XL and Fotis

Yes of course I should be checking the D column for both values!

Thanks to both of you.


Alf

[Kevin UK]

@ Alf

FDibbins, was correct using the SUMIFS. The OP uploaded a xlsx file! So why not use the SUMIFS. There are to many people on here suggesting that they use the SUMPRODUCT when the OP's have Excel 2007> and probably confusing the matter.

[Alf]

Hi Kevin

FDibbins, was correct using the SUMIFS.

To me it was not a question of right or wrong rather testing an alternative method to solve a specific problem.

But since a part of my work is based on Excel 2003 I prefer choosing solutions that works in both environment i.e. Excel 2003 and 2007.

Alf

[Fotis1991]

You are welcome Alf. Thanks for the reb*.

I'd like also to say this.

I have seen you Kevin in many threads to insist that if someone is using Excel >2003, is wrong to use SUMPRODUCT and need to use SUMIFS or COUNTIFS.

I agree that is better to use these(are more fasters than SUMPRODUCT), BUT there are some cases(like this that Alf mentioned and also some mores) that SUMPRODUCT helps more.

And of course in any case no one can says that SUMPRODUCT using is wrong!