SUMIF Help

[paul29berks]

I cannot for the life of me get this formula to work

=SUMIF('Actuals 2004'!$A:$A,'Jan 2004'!$A$1&'Jan 2004'!$A$2&'Jan 2004'!$A4&'Jan 2004'!$B4&'Jan 2004'!$C4&'Jan 2004'!E3,'Actuals 2004'!$H:$H)

Actuals 2004 being where data is held. I have concatenated all fields.

Can anyone help?

Thanks

 

[fairwinds]

Hi,

Formula is OK and works for me. So:

Check your ranges and data types.

 

[paul29berks]

Hello

The ranges seem fine, but the source file "Actuals 2004" has been copied across from Access - how can I check the data type?

Thanks

 

[just_jon]

Got any leading/trailing spaces in your data range?

 

[fairwinds]

The H column must be numbers, can be checked by =ISNUMBER() formula.

The other fields you must check for extra spaces etc that prevents an exact match.

 

[paul29berks]

Hello again

Have check H:H which areall numbers. Have checked spaces which seem to be ok. Could there be another reason?

thanks

 

[just_jon]

Can you isolate a few that should create a match to your SUMIF criteria, and apply =LEN() to those cells?

Alternativey, search this board for the TRIMALL macro - you could run it and see if that fixed your problem.