Array formula returns 0

[davrmars]

My formula is {=SUM(IF(($D$4:$D$100=$D$1)*($B$4:$B$100=$B$1),$G$4:$G$100,0))} it keeps returning a blank cell. The B column has account numbers. The D column has customer numbers and the G column has sales numbers. All are formatted as numbers. No letters, not formatted as text, no dates, auto calculation is on. What other reasons could this not be working?

 

[davrmars]

What does this return..

=INT(D4)=D4

Repeated on ALL the numeric Values.

All come back True. This is blowing my mind. I have array formulas on a different section of same worksheet that work fine. Nothing is locked, protected, validated. This just does not make sense.

 

[Jonmo1]

PM me if you want to email the file to me.

 

[barry houdini]

Does the formula return zero or blank, I'm not clear?

Sometimes you may get zero as an erroneous formula result when there are circular references somewhere in the sheet, is that possible?

 

[davrmars]

Does the formula return zero or blank, I'm not clear?

Sometimes you may get zero as an erroneous formula result when there are circular references somewhere in the sheet, is that possible?

It returns blank. sorry for confusion.

 

[Jonmo1]

It returns blank. sorry for confusion.

do you have the option "Show Zero Values" UNChecked in the Excel Options?
I.E. If you just type a zero by hand in a cell, do you end up seeing the zero after you press enter?

 

[davrmars]

Thank you all for your help. I am giving up and moving on. I will re-create the data in a new worksheet as it seems to work outside this sheet. Same file, just different sheet. Very Bazaar. Thank you anyway.