SUMIFS problem

[StephenZ4]

I have a data sheet "FX Share Data" and an Analysis sheet.

I am suming data between 2 dates.

EUR is a column of currency prices
FXDate is a column of Dates

If I put the formula on the Data sheet =SUMIFS(EUR,FXDATE,">"&Analysis!$J$4,FXDATE,"<="&Analysis!$O$4) the Answer is 328.982

If I put the formula on the Analysis sheet =SUMIFS(EUR,FXDATE,">"&Analysis!$J$4,FXDATE,"<="&Analysis!$O$4) the answer is 0.000

This makes no sense!

Steve

 

[FDibbins]

What happens if you replace the range names with actual cell refs?

 

[StephenZ4]

The criteria ">"&VALUE(Analysis!$J$4) are coming up #value ! on the analysis sheet when I look at function help.

 

[StephenZ4]

Same thing. It's the criteria that isn't working

 

[StephenZ4]

If I put ">42000" it works. If I put ">"&A1, which has 42000 in it, I get #value

 

[StephenZ4]

It doesn't seem to like having criteria on a different sheet.

I've used sumproduct as an inelegant work around:

=(SUMPRODUCT((FXDATE>Analysis!$J$4)*(EUR))-SUMPRODUCT((FXDATE>Analysis!$O$4)

 

[FDibbins]

If I put ">42000" it works. If I put ">"&A1, which has 42000 in it, I get #value

Are you sure that the criteria range is actually values, and not text looking like numbers?
test a few with =isnumber(cell-ref) if you get FALSE, your "numbers" are text

 

[StephenZ4]

Yes. Definitely numbers. Comes up true. Just something weird with excel. sumproduct works fine, sumif and sumifs don't.