Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: sumproduct with 3 criteria - month, year and text
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Nov 2014
    Location
    East Midlands UK
    Posts
    201
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sumproduct with 3 criteria - month, year and text

    Hi

    I have just worked on the following and though I haven't included the check on "Yes" at the moment it works.. have I gone crazy or you agree

    =SUMPRODUCT((MONTH(Assets!$I$5:$I$305)=MONTH(1&H$5))*(YEAR(Assets!$I$5:$I$305)=($H$3))*(Assets!$G$5))


    noting that the year is a number in a drop down list but the month is a text in a drop down list so I changed the way H$3 is handled.

  2. #12
    Board Regular
    Join Date
    Nov 2014
    Location
    East Midlands UK
    Posts
    201
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: sumproduct with 3 criteria - month, year and text

    Quote Originally Posted by F0RE5T View Post
    Hi

    I have just worked on the following and though I haven't included the check on "Yes" at the moment it works.. have I gone crazy or you agree

    =SUMPRODUCT((MONTH(Assets!$I$5:$I$305)=MONTH(1&H$5))*(YEAR(Assets!$I$5:$I$305)=($H$3))*(Assets!$G$5))
    Hi

    I have tested the code below with changing year, month and "Yes" to other text and the values returned are perfect.....

    Many thanks for your support as I have learnt a lot.

    Forest

  3. #13
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,091
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: sumproduct with 3 criteria - month, year and text

    Quote Originally Posted by F0RE5T View Post
    Hi

    I have tested the code below with changing year, month and "Yes" to other text and the values returned are perfect.....

    Many thanks for your support as I have learnt a lot.

    Forest
    =Assets!$G$5 * SUMPRODUCT(--(Assets!$I$5:$I$305-DAY(Assets!$I$5:$I$305)+1=DATE($H$3,$H$5,1)))

    If you the Yes condition also add:

    =Assets!$G$5 * SUMPRODUCT(--(Assets!$I$5:$I$305-DAY(Assets!$I$5:$I$305)+1=DATE($H$3,$H$5,1)),--(Assets!$H$5:$H$304="Yes"))


    Last edited by Aladin Akyurek; Jun 18th, 2019 at 03:59 AM.
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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