SUMIF Help

paul29berks

Active Member
Joined
Mar 15, 2004
Messages
293
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Formula is OK and works for me. So:

Check your ranges and data types.
 

paul29berks

Active Member
Joined
Mar 15, 2004
Messages
293
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
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

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

Active Member
Joined
Mar 15, 2004
Messages
293
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

Legend
Joined
Sep 3, 2002
Messages
10,473
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.
 

Forum statistics

Threads
1,147,673
Messages
5,742,530
Members
423,736
Latest member
dracula cyrus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top