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")

 

[Johnny C]

If the dates in Col A are in date format, try

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

Dates are stored as integers, the number of days since 1/1/1900. So any formula referencing a date needs to use the integer date value

You could also try

=SUMIFS($AB1:AB1999,$A1:$A1999,DATE(2017,9/7))

HTH

 

[zone709]

Hi it still returns zero and in column A the format is date

 

[Johnny C]

Have you tried the =SUMIFS($AB1:AB1999,$A1:$A1999,DATE(2017,9,7)) version?

Also, it's not obvious where you are but is your Windows date format dd/mm/yyyy or mm/dd/yyyy? that makes a difference.

If you've pasted column A from Access it will have used the MM/DD/YYYY format even if you've got DD/MM/YYYY as a windows setting.

 

[zone709]

yeah it said you enter to many arguments. I also just changed the format but nothing. Ill try to keep getting it to work

 

[Johnny C]

IDK what's wrong then, I tried both and they worked fine for me.

 

[Aladin Akyurek]

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")

Does this...

=SUMIFS($AB1:AB1999,$A1:$A1999,DATE(2017,7,9))

or this succeed?

=SUMIFS($AB1:AB1999,$A1:$A1999,DATE(2017,9,7))

 

[zone709]

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

 

[Jonmo1]

Are your dates in A real dates ?
Test with =ISNUMBER(A1) and filled down
Are they True or False ?

If True, do your dates also contain time ?
Test with
=INT(A1)=A1 and filled down
Are they True or False ?

 

[zone709]

False