Fomula ? on SumIfs using date

[zone709]

Hi trying to use this by date "07/09/17"), but not working returns 0. Probably need a miner adjustment here. Any help thanks

=SUMIFS($AB1:AB1999,$A1:$A1999,"07/09/17")

 

[Aladin Akyurek]

returns 0. I must have something in column A the format that's not making this work. I change to short dat long custom etccccc

What do these return?

=COUNT(AB1:AB1999)

=COUNT(A1:A1999)

=COUNTIFS(A1:A1999,DATE(2017,7,9))

=COUNTIFS(A1:A1999,DATE(2017,9,7))

 

[Jonmo1]

So ISNUMBER is false, your dates in A are not really dates. Just text strings that look like dates.

Need to convert them somehow.
Try copying a blank cell, highlighting the column, right click - Paste Special - Values - Add - OK.

 

[zone709]

ok thanks so they are text strings then. It seems like the data being pulled in is as text strings. So now I need to convert it to paste special. Yes I can record something like you stated above, but is there any easier way maybe to highlight columns and change it and get it out of text strings?

 

[Jonmo1]

You can also try Data - Text To Columns - Delminiated, Finish.

 

[zone709]

ok I'm good now guys thanks for the feedback. Finding out they were a text string was the kicker.

 

[Jonmo1]

You're welcome.

 

[Johnny C]

If it's a string, you could use =DATEVALUE() to convert to a date