SUMPRODUCT Writing Error

[excellenthelp]

I am wondering what I am writing inaccurately here:

Please Login or Register  to view this content.

This returns as #VALUE!.

D:D needs to equal 1.
H:H needs to be equal or between 4000 and 4999.

Help, please. =D

[Ace_XL]

Try

=SUMPRODUCT('GDetail FY13'!$I:$I*('GDetail FY13'!$D:$D=1)*('GDetail FY13'!H:H>=4000)*('GDetail FY13'!H:H<=4999))
or
=SUMIFS('GDetail FY13'!$I:$I,'GDetail FY13'!$D:$D,1,'GDetail FY13'!H:H,">="&4000,'GDetail FY13'!H:H,"<="&4999)

[Jonmo1]

between 4000 and 4999 needs to be written as 2 seperate criteria..
>= 4000
and
<=4999

=SUMPRODUCT(('GDetail FY13'!$I:$I)*('GDetail FY13'!$D:$D=1)*('GDetail FY13'!H:H>=4000)*('GDetail FY13'!H:H<=4999)*-1)

However, even though XL2007+ allows it, I highly recommend NOT using entire column refs like H:H.
Restrict it to your actual used range H1:H10000

[excellenthelp]

Thank for the help.. However the formulas given by Ace_XL and Jonmo1 does not work.

Here is the updated formula after considering the given formulas above. Still does not work. Still returns as #VALUE!.

Please Login or Register  to view this content.

[Jonmo1]

If there are any TEXT values in colum I, it will result in #Value!
This is often caused because row 1 is headers.
Try making all the ranges begin in row 2 instead of 1.

[excellenthelp]

Ahh.. Yes, this got rid of the #VALUE! error. But it still returns nothing, just an zero (or a dash in this case).

Current formula in C7:N7 in GReport. that needs to be edited.

Please Login or Register  to view this content.

Attached is the sample of what I am working on.

SampleHelp.xlsx

[Jonmo1]

Columne H are not really numbers, they are "Numbers Stored As Text"

Indicated by the little green triangles at top left corner of each cell...

Copy a blank cell
Highlight Column H
Right click - Paste Special - Values - Add

[excellenthelp]

Thanks! It was indeed Numbers Stored As Text. Used Text to Column method to address this issue.

Marking this as SOLVED.

[excellenthelp]

Thanks! It was indeed Numbers Stored As Text. Used Text to Column method to address this issue.

Marking this as SOLVED.

[Jonmo1]

You're welcome. Thanks for the feedback.