Share via

Sumproduct with Current Date

Anonymous
2019-03-25T19:15:59+00:00

I have the following formula in cell b4:

=SUMPRODUCT((MONTH(A7:A28)=3)*(YEAR(A7:A28)=2019)*(I7:I28)*(M7:M28<>"Yes"))

I would like to substitute the MONTH and YEAR functions with the TODAY function.

Thank you for the help.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-03-25T19:34:33+00:00

    [Correcting some critical misunderstandings....]

    First, alternatively for March 2019:

    =SUMIFS(I7:I28, M7:M28, "<>yes", A7:A28, ">=" & DATE(2019,3,1), A7:A28, "<=" & DATE(2019,3,31))

    For today's month:

    =SUMIFS(I7:I28, M7:M28, "<>yes", A7:A28, ">=" & EOMONTH(TODAY(),-1)+1, A7:A28, "<=" & EOMONTH(TODAY(),0))

    or

    =SUMIFS(I7:I28, M7:M28, "<>yes", A7:A28, ">" & EOMONTH(TODAY(),-1), A7:A28, "<=" & EOMONTH(TODAY(),0))

    In the second form (better, IMHO), be mindful of ">" (greater than) v. ">=" (greater or equal).

    Nitpick....  It is risky to call TODAY() multiple times.  In the last "tick" (15.625 msec) before midnight, the result can be different values for TODAY(), resulting in inconsistencies among the several calculations.  Arguably, extremely unlikely; but I've seen it affect users.  Technically, better to enter =TODAY() into a single cell and to have multiple references to it.

    Was this answer helpful?

    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  3. Vijay A. Verma 104.9K Reputation points Volunteer Moderator
    2019-03-25T19:17:35+00:00

    Hi Carol

    I am Vijay, an Independent Advisor. I am here to work with you on this problem.

    Use the below formula

    =SUMPRODUCT((MONTH(A7:A28)=MONTH(TODAY()))*(YEAR(A7:A28)=YEAR(TODAY()))*(I7:I28)*(M7:M28<>"Yes"))

    Do let me know if you require any further help on this. Will be glad to help you.

    Was this answer helpful?

    0 comments