COUNTIFS DATE does not work

The criteria for you second check is not truly a criteria. Excel does not know if you are looking for something equal to, less than, greater than, or not equal to. This is a way to do a range for 2013 dates:
=COUNTIFS(L2:L31,"<1/1/2014",L2:L31,">12/31/2012")

You can use a similar method for the month check.

For the specific days, you should be able to use just the check for TODAY() or TODAY()-1

You might be better using SUMPRODUCT ...

Total amounts in column B for dates in column A in 2012:

=SUMPRODUCT(--(B$2:$B$31), --(YEAR($A$2:$A$31)=2012))

Total amounts in column B for dates in column A in 2012 and month of November:

=SUMPRODUCT(--(B$2:$B$31), --(YEAR($A$2:$A$31)=2012),--(MONTH($A$2:$A$31)=11))

Year to date:

=SUMPRODUCT(--(B$2:$B$31),--(YEAR($A$2:$A$31)=YEAR(TODAY())))

Current month to date:

=SUMPRODUCT(--(B$2:$B$31),--(YEAR($A$2:$A$31)=YEAR(TODAY())),--(MONTH($A$2:$A$31)=MONTH(TODAY())))


It's usually easier for everyone if you provide a sample workbook. Then we can see your data and the workbook structure.


Regards, TMS

hi Rene, welcome to the forum. you could consider SUMPRODUCT
current year
=SUMPRODUCT((YTD_CM!B:B=B$3)*(TEXT(YTD_CM!G:G,"yyy")=TEXT(TODAY(),"yyy")))

current year, current month
=SUMPRODUCT((YTD_CM!B:B=B$3)*(TEXT(YTD_CM!G:G,"yyym")=TEXT(TODAY(),"yyym")))

today
=SUMPRODUCT((YTD_CM!B:B=B$3)*(YTD_CM!G:G=TODAY()))

yesterday
=SUMPRODUCT((YTD_CM!B:B=B$3)*(YTD_CM!G:G=TODAY()-1))

COUNTIFS can also do it. formula's a little longer but works faster:
=COUNTIFS(YTD_CM!B:B,B$3,YTD_CM!G:G,">="&DATE(YEAR(TODAY()),1,1),YTD_CM!G:G,"<="&DATE(YEAR(TODAY()),12,31))
=COUNTIFS(YTD_CM!B:B,B$3,YTD_CM!G:G,">="&EOMONTH(TODAY(),-1)+1,YTD_CM!G:G,"<="&EOMONTH(TODAY(),0))
you should get the drift

i do not recommend ranging the whole columns though. slows down Excel. use a big range you wont use up instead. like "YTD_CM!B2:B1000"

Dear benishiryo, TMShucks and Pauleyb,

Thanks so much for your help, I will now go through everything in detail and come back to you, and of course if it's still open I'm going to provide an example workbook.

Thanks a lot, very much appreciated!

Rene

---

Update: I've found a reason why today() is not working for me, my date contains the Time too.

Since it got to complicated to explain I also added an Sample File

1Forum_Sample_ReneHelp.xlsx

could one of you have a look?

FYI - I cannot change the Source Data, this comes from an external Database I can only add formulas (like I did with the Red Text).

Again, Thanks a lot for your help.

Cheers,
Rene

you're very welcome. try this:
=SUMPRODUCT((YTD_CM!B:B=B$3)*(TEXT(YTD_CM!G:G,"yyymd")=TEXT(TODAY(),"yyymd")))

basically what i suggested to Rene was:
- COUNTIFS instead of SUMPRODUCT to calculate faster
- not range up the whole column
- consider timing in the calculation

so to calculate yesterday, here's my solution:
=COUNTIFS(YTD_CM!B$1:B$10000,B$3,YTD_CM!G$1:G$10000,">="&TODAY()-1,YTD_CM!G$1:G$10000,"<"&TODAY())