+ Reply to Thread
Results 1 to 15 of 15

Countifs in combination with Today() (Format issue?)

  1. #1
    Forum Contributor
    Join Date
    05-28-2020
    Location
    Poland
    MS-Off Ver
    O365,
    Posts
    120

    Countifs in combination with Today() (Format issue?)

    Dear Pros,

    I am struggling with Countifs trying to combine it with Today()

    What I have figured out until now and what is working while testing is the following:

    =COUNTIFS(Check[Agent],A29,Check[Date],">=01.07.2020",Check[Date],"<=31.07.2020",Check[Agent],A29,Check[Strike],"Yes") --- works like a charm
    =COUNTIFS(Check[Agent],A29,Check[Date],"=23.07.2020",Check[Agent],A29,Check[Strike],"Yes") --- works

    As you can see, I am counting in a table called "Check", if an "Agent" has a "Yes" within column "Strike" and check it with a date range in the first example,
    while in the second, with a specific date.

    Target of the formula should be, to check from "Today" back 28 days (4 weeks), if an Agent got striked with a Yes.

    When trying and testing just to use Today(), I am not successful:

    =COUNTIFS(Check[Agent],A29,Check[Date],"="&Today(),Check[Agent],A29,Check[Strike],"Yes") --- does not work
    =COUNTIFS(Check[Agent],A29,Check[Date],"<="&Today()-28,Check[Agent],A29,Check[Strike],"Yes") --- therefore also does not work

    Additionally I found out the following:
    If I for example use in Cell I1 Today() and either format the cell for an European date like 23.07.2020 or change it to 23/07/2020 or change it to amercian format 07/23/20 and
    refer to it with the following format:

    =COUNTIFS(Check[Agent],A29,Check[Date],"="&I1,Check[Agent],A29,Check[Strike],"Yes") --- it does not work

    But if I replace the date with 23.07.2020 into Cell I1, then it works.

    Therefore my question, what am I doing wrong? Am I using the formula incorrectly, do I have a Format issue, or what is wrong with it?

    I am running Office 365

    Can you please help?

    Thank you

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,906

    Re: Countifs in combination with Today() (Format issue?)

    Hi, welcome to the forum

    What you need to understand about dates and times in excel is…

    a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Thu 23 Jul 2020) is actually 44035

    Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

    That said, if you have a real date, as opposed to a text date, it shouldnt matter what date format you use.

    2. if you are using a specific value (or date), you dont need to use "="&...
    =COUNTIFS(Check[Agent],A29,Check[Date],Today(),Check[Agent],A29,Check[Strike],"Yes")
    However, you would probably need to ref from today onwards, so...
    =COUNTIFS(Check[Agent],A29,Check[Date],">="&Today(),Check[Agent],A29,Check[Strike],"Yes") would be correct.

    Why the formula is not working is hard to say without seeing some sample data, but a few questions,,,
    ...is today's date actually in your data range?
    ...are your dates actual dates and not text dates. test some with =isnumber(cell-ref). FALSE indicates a text date

    3. when all else fails, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    05-28-2020
    Location
    Poland
    MS-Off Ver
    O365,
    Posts
    120

    Re: Countifs in combination with Today() (Format issue?)

    Dear Ford,
    Thank you very much for helping me out. As I can see, I learn more and more on a daily basis.
    And one thing is not clear to me regarding the text date.

    I did the test and as you assumed correctly, I have text dates? And here comes my confusion.

    When I check my Cell, it is formated the following way:
    Attachment 687897

    And funny is, I have no clue how Armenian got in here.
    But receiving a FALSE through the isnumber, how do I change my date into a date? What am I doing wrong there?

    Thank you

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,364

    Re: Countifs in combination with Today() (Format issue?)

    May be you can try to convert today into text format

    =COUNTIFS(Check[Agent],A29,Check[Date],text(Today(),"dd.mm.yyyy"),Check[Strike],"Yes")

    I removed 1 extra couple (Check[Agent],A29) out of the COUNTIFS
    Quang PT

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,442

    Re: Countifs in combination with Today() (Format issue?)

    Quote Originally Posted by mstgier;5369008

    When I check my Cell, it is formated the following way:
    [ATTACH
    687897[/ATTACH]

    There was nothing attached to your post. You need to follow the " Go advanced " link to attach files ( the paperclip is U/S)
    Formatting is the way XL shows things to you, it has nothing to do with underlying values
    Perhaps post a sample sheet? ( see yellow banner) as there are many ways to make date text real dates

  6. #6
    Forum Contributor
    Join Date
    05-28-2020
    Location
    Poland
    MS-Off Ver
    O365,
    Posts
    120

    Re: Countifs in combination with Today() (Format issue?)

    Dear Bebo021999

    That is very interesting and is working for me. May I ask, how would I extend your formula, to check for example last 7 days including today?

    Usually I found some formulas, which are:

    Today()-7 but they start with ">=", so not sure, how to build that in?

  7. #7
    Forum Contributor
    Join Date
    05-28-2020
    Location
    Poland
    MS-Off Ver
    O365,
    Posts
    120

    Re: Countifs in combination with Today() (Format issue?)

    OH I am sorry Pepe Le Mokko,
    I used the Insert Image. I try again with the Advanced Button

    I did go through Manage Attachment, how it did attach it now
    Attached Images Attached Images

  8. #8
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,442

    Re: Countifs in combination with Today() (Format issue?)

    Better to attach your sheet as suggested ( see yellow banner)

  9. #9
    Forum Contributor
    Join Date
    05-28-2020
    Location
    Poland
    MS-Off Ver
    O365,
    Posts
    120

    Re: Countifs in combination with Today() (Format issue?)

    I will Pepe,
    if Bebo is not able to answer. There is a lot of confidential data in it, I need to remove and shrink it down.
    But I will do so if necessary, because I want to learn and understand.

    Thank you
    PS: (Skyrim, love it

  10. #10
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,442

    Re: Countifs in combination with Today() (Format issue?)

    No worries
    I don't like COUNTIFS ( my preference goes to SUMPRODUCT), so I can't help there

  11. #11
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,364

    Re: Countifs in combination with Today() (Format issue?)

    Quote Originally Posted by mstgier View Post
    Dear Bebo021999
    That is very interesting and is working for me.
    Today()-7 but they start with ">=", so not sure, how to build that in?
    "Date_in_text" Range = "Date_in_text" => OK
    But
    "Date_in_text" Range >= "Date_in_text" => wrong result

    Therefore try to upload a small piece of the worksheet (without sensitive data) to see what we can do

  12. #12
    Forum Contributor
    Join Date
    05-28-2020
    Location
    Poland
    MS-Off Ver
    O365,
    Posts
    120

    Re: Countifs in combination with Today() (Format issue?)

    Good morning,
    Please find an example to my question in the attachment.

    I try in the second sheet, to count, how many strikes someone get.
    Target is, to count the yes strikes the last 7 days. I tried to enter
    the "<=" but Excel gave me all the time an error, that I have to
    change my formula.

    Maybe you have an idea how to solve it?

    Thank you very much
    Attached Files Attached Files

  13. #13
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,364

    Re: Countifs in combination with Today() (Format issue?)

    Try:

    Please Login or Register  to view this content.

  14. #14
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Countifs in combination with Today() (Format issue?)

    Please try at D3

    =SUMPRODUCT(COUNTIFS(Check[Agent],A3,Check[Date],TEXT(ROW(INDEX(A:A,TODAY()-7):INDEX(A:A,TODAY())),"dd.mm.yyyy"),Check[Strike],"Yes"))
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    05-28-2020
    Location
    Poland
    MS-Off Ver
    O365,
    Posts
    120

    Re: Countifs in combination with Today() (Format issue?)

    Hello Bo_Ry,
    Thank you very much for sending the solution. I tried it on my side and it works like a charm. Looks like I have to look into INDEX. I haven't used that so far, but there is always something to learn.
    Again a thank you to you and also the other participants.

    A pleasure to talk to all of you,
    Thank you
    Mike

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. SUBTOTAL and COUNTIFS combination
    By aaron_burr in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-07-2020, 12:52 AM
  2. [SOLVED] Countifs > Today()
    By KErasmus in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-23-2019, 07:49 AM
  3. [SOLVED] Too few arguments - combination of COUNTIFS and OR
    By ell_ in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-01-2018, 11:53 AM
  4. COUNTIFS before today and after 01/01/15
    By -jack in forum Excel General
    Replies: 3
    Last Post: 12-13-2016, 12:11 PM
  5. Format issue with changing a number letter combination to a date
    By Newbiedine in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-13-2014, 01:34 AM
  6. Vba: Using Evaluate Sumproduct / Countifs combination for Date
    By vidyuthrajesh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-29-2012, 07:44 PM
  7. Replies: 3
    Last Post: 12-11-2007, 01:36 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1