+ Reply to Thread
Results 1 to 9 of 9

Sum Product By Month & By Specific Text

  1. #1
    Registered User
    Join Date
    02-08-2016
    Location
    CANADA
    MS-Off Ver
    2013
    Posts
    11

    Sum Product By Month & By Specific Text

    I currently have this formula: =SUMPRODUCT(($E$2:E82)*(MONTH($B$2:B82)=1))

    I need to add the additional criteria to my formula: IF D2:D70 = "TBY"

    I can't figure out how to get that additional criteria to sum product only if Column D equals TBY.

    Please help.

    Thank you!

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,467

    Re: Sum Product By Month & By Specific Text

    Hi Chrystal,

    Try

    =SUMPRODUCT(($E$2:E82)*(MONTH($B$2:B82)=1)*($D$2:D82="TBY"))

    The range in rows of all arrays must be the same.
    One test is worth a thousand opinions.
    Click the * below to say thanks.

  3. #3
    Registered User
    Join Date
    02-08-2016
    Location
    CANADA
    MS-Off Ver
    2013
    Posts
    11

    Re: Sum Product By Month & By Specific Text

    I get a #Value error when I enter that formula
    .

    I have three options only for the D column of TBY, CGY, MTL but some rows aren't completed yet so cells are Blank in all three columns.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,467

    Re: Sum Product By Month & By Specific Text

    Are all the cells in column E numbers?
    Are all the cells in column B dates?
    Are all the cells in column D Text?

  5. #5
    Registered User
    Join Date
    02-08-2016
    Location
    CANADA
    MS-Off Ver
    2013
    Posts
    11

    Re: Sum Product By Month & By Specific Text

    Yes

    Column E = Numbers Only (100.001)
    Column B = Dates Only (Only 2016 dates)
    Column D = Text Only (TBY, CGY, MTL - as a drop down menu option between the three)

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,519

    Re: Sum Product By Month & By Specific Text

    Please post a sample Excel file with your #N/A errors.

    I tested your formula (with blank cells) and it worked fine so suggests erroneous data type somewhere.

  7. #7
    Registered User
    Join Date
    02-08-2016
    Location
    CANADA
    MS-Off Ver
    2013
    Posts
    11

    Re: Sum Product By Month & By Specific Text

    B D E
    DATE DEST WEIGHT
    1-Feb CGY 100.694
    4-Feb MTL 96.162
    11-Feb MTL 95.990
    12-Feb CGY 91.761
    12-Feb TBY 96.192
    13-Feb CGY 100.095
    14-Feb CGY 96.272
    14-Feb TBY 94.806
    16-Feb CGY 96.258
    17-Feb TBY 96.142
    BLANK BLANK BLANK
    BLANK BLANK BLANK...


    Below in Rows 100-120 I have a chart in Columns A-E

    In my equation I have the range specified to end at Row 82 (all arrays are 2:82 so they all equal)

    Thank you!

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,519

    Re: Sum Product By Month & By Specific Text

    I had no problem with the above data: we are not going to resolve this unless you post your actual Excel file.

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    12,322

    Re: Sum Product By Month & By Specific Text

    and after adding the file, as requested in #7.

    I would use a pivot table to solve this problem.
    Notice my main language is not English.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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