0

I have the following formula:

=COUNTIFS(Transactions2023[Result], "Win", Transactions2023[League], "NBA", Transactions2023[Date], "<=" & "4/11/2023")

I actually need for all the values before or on 4/11/2023 at 5 PM to be counted, however I have the time listed in a separate column (Transactions2023[Time]). Is there a way for me to accomplish this in the formula? So I can AND the value from the date and time column together?

UPDATE: Per the kind folks below, have come up with:

=COUNTIFS(Transactions2023[Result], "Win", Transactions2023[League], "NBA", Transactions2023[Date] + Transactions2023[Time], DATEVALUE(Transactions2023[Date]) + TIMEVALUE(Transactions2023[Time]) <= 45027 + (17/24))

But I get the standard formula error. I've checked my ps and qs and everything seems to be fine, so wondering if I screwed up the syntax somewhere.

Improve this question
15
  • 2
    COUNTIFS is AND so just add: Transactions2023[Time],"<=" & TIME(17,0,0) to the countifs.
    – Scott Craner
    Commented May 2, 2023 at 21:44
  • 1
    @YisroelTech It doesn't give me the correct result because there are other rows that are not 4/11/2023 but are 5 PM or greater and it knocks those out.
    – Ser Pounce
    Commented May 3, 2023 at 3:46
  • 1
    Got you. The Excel serial date code is explained here exceljet.net/glossary/excel-date
    – Yisroel Tech
    Commented May 3, 2023 at 4:01
  • 1
    @YisroelTech Cool appreciate it, thanks for all your help.
    – Ser Pounce
    Commented May 3, 2023 at 4:05
  • 1
    The reference to the range.
    – Yisroel Tech
    Commented May 3, 2023 at 4:15

1 Answer 1

Reset to default
1

With the latest version of your formula, the issue is at Transactions2023[Date] + Transactions2023[Time], DATEVALUE(Transactions2023[Date]) + TIMEVALUE(Transactions2023[Time]) <= 45027 + (17/24)

DATEVALUE(Transactions2023[Date]) + TIMEVALUE(Transactions2023[Time]) isn't needed there.

There correct criteria:
..., Transactions2023[Date] + Transactions2023[Time], "<=" & 45027 + (17/24))

Improve this answer
4
  • Thanks. I did try a simplified version "=COUNTIFS(Transactions2023[Result], "Win", Transactions2023[Date] + Transactions2023[Time], "<=" & 45027 + (17/24))" and it still didn't work. I'm using Excel 365.
    – Ser Pounce
    Commented May 3, 2023 at 17:04
  • Please specify how exactly it doesn't work
    – Máté Juhász
    Commented May 3, 2023 at 20:31
  • It's giving the stock error message of a formula not working, but I see that it's also highlighting the left paranthesis in red in (17/24).
    – Ser Pounce
    Commented May 4, 2023 at 0:46
  • maybe you don't have the right number of parentheses? Can't tell for sure without seeing your full formula. Best would be to update your question with latest formula and screenshot of the error message
    – Máté Juhász
    Commented May 4, 2023 at 6:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .