Excel 2010 Comparing column values

I am trying to use the sumifs() statement to add up values of a column. I have the following worksheet called January in Excel 2010:
_____________A_____________________ B ___________
1_________ABC Company_______________100
2_________Abc Company_______________100
3_________abc Company_______________100
4_________Widget Company____________100
5_________Widget Com________________100
6_________Widget Comps______________100

On the Main worksheet I am trying to sum the values for each company:
___________ A __________________ B ________________________
1_______ABC Company___________=SUMIFS(January!$B:$B,January!$A:$A,A1)
2_______Widget Company_________=SUMIFS(January!$B:$B,January!$A:$A,A2)

When I do it this way I get $100 for each value in column B of the main worksheet since the names in column A of the main worksheet don’t match the names in column A of the January worksheet. I want to use the Upper() command and the Mid() command to try and match the first 8 values of column A of the main sheet with the first 8 characters in column A of the January worksheet. I want to compare Upper(Mid(A1,1,8)) with the first 8 characters of the January worksheet column A. How can I accomplish this?

There is nothing wrong with the formulae in the workbook. There is however a problem with the data on both the January and February worksheets. The names of the companies are not consistent and the ABC Company also has spaces at the end of the name. These problems will cause the formulae to fail. When the data is corrected, the formulae work properly.

Hi and welcome to the forum

If you remove the trailing spaces in the date headings on Main, you can use this, copied down and across...
=SUMIFS(INDIRECT(B$5&"!B:B"),INDIRECT(B$5&"!A:A"),LEFT($A8,8)&"*")

The LEFT() is pulling the 1st 8 characters from the name on the left - the more characters you can match, the more specific it will be

Is this what you are trying to do?
In B8, drag across to C8 then down.

Formula:

Please Login or Register  to view this content.

Remember to clean all of your data strings

[EDIT]
This is the same as Post#5 by FDibbins
I'll leave it for the example workbook.

worksheet attached