Sum product not working properly

[Taff]

Guys,

any chance you could give me some pointers?

I have copied the below formula from another work book, where it worked fine.

However it keeps returning a #Div/0! error in my new work book.

=SUMPRODUCT((Data!$E$9:$E$4500=$B$3)*(Data!$I$9:$I$4500=F$17)*(Data!$N$9:$N$4500>$B18)*(Data!$N$9:$N$4500<=$C18))

Its driving me up the wall trying to figure out why.

There is info in the cells, the formula is pointing at the correct areas..... aaarrggghhhhhh

Appreciate any ideas.

 

[Dave Patton]

Did you show the full formula?
The formula that you showed shows a count of the data that meets the criteria.
Try using Excel's Formula Evaluate to review the formula with a concise example.

Excel 2010
	A	B
1	2	2
Data

Cell Formulas
Range	Formula
A1	=SUMPRODUCT((Data!$E$9:$E$4500=$B$3)*(Data!$I$9:$I$4500=F$17)*(Data!$N$9:$N$4500>$B18)*(Data!$N$9:$N$4500<=$C18))
B1	=SUMPRODUCT(--(Data!$E$9:$E$4500=$B$3),--(Data!$I$9:$I$4500=F$17),--(Data!$N$9:$N$4500>$B18),--(Data!$N$9:$N$4500<=$C18))

It will help if you post a concise example of your challenge complete with expected result.

 

[Drrellik]

I copied and pasted your formula into a blank sheet and left the name sheet1 and created a 2nd sheet named data and populated the three columns with data E,I, & N and on sheet one put data in the four other fields, I was never able to get a result other than 0 in your formula even using CTRL+SHIFT+ENTER but I never got a DIV/0 error make sure when you copy and paste you have sheets named properly and data is in the right cells / columns

 

[Aladin Akyurek]

@ Taff

You probably have a #DIV/0! error somewhere.

 

[Taff]

Sorted.

it was one simple multiplication error, in amongst 4500 lines.

I'm surprised you didn't hear the cheers across the globe from me.

Thanks for your suggestions.