sumifs function not working

[ammartino44]

Hello. If I write this formula =SUMIFS(B3:AK3,B1:AK1,"=2013") it works right but if instead of 2013, I refer to a cell that has 2013 in it. It doesn't work. Why is this?

[adhawan06]

What are you referring to. Date or Year or text?

[NeedForExcel]

Maybe because the Data is having numbers stored as text.. Please attach your sample workbook.. or instead of putting 2013 in the Cell, try putting '2013 to check if that is the problem..

[adhawan06]

If a Cell Containing Date, then try the following formula. Assuming you have date in A1 cell -

Please Login or Register  to view this content.

If a Cell Containing text, then try the following formula. Assuming you have text in A1 cell -

Please Login or Register  to view this content.

[ammartino44]

Spreadsheet attached.

[NeedForExcel]

Hi,

Put this in Cell F7

Please Login or Register  to view this content.

However, I do not understand 1 thing, the post is about SUMIFS, then why is your sheet showing COUNTIF?

Secondly when you input a Text, you input it like =SUMIFS(B3:AK3,B1:AK1,"XYZ"), when you input a Number, you input it like =SUMIFS(B3:AK3,B1:AK1,2013) & when you input a cell reference, you input like =SUMIFS(B3:AK3,B1:AK1,I4)

In you sheet, you input a Cell reference with Double Quote, which makes it a Text String instead of Cell Reference

[adhawan06]

Agreed with @NeedForExcel, Your post is stating you are looking for sumifs function however, your sheet is having Countif. Please change the subject of this thread first.

Second, as said in above post by NeedForExcel, Cell Reference never put in between "" or with "=" sign. You can just keep it like actual reference. If you want any given inputs (in the function only) to work as criteria then you can use both the symbol (its again depend on situation).

Third, You kept your criteria in B4 and then don't know why you have taken a cell reference to I4. The Countif function work Range (The Range in which you wanted to take the count) and then Criteria (The Criteria which you wanted to match in that given range) is very simple.

Hope this will make more clear picture.

Cheers!!!

Anil