+ 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
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    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 * Add Reputation 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
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    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
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    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
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    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
    Office 365
    Posts
    14,987

    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.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

+ 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. [SOLVED] Remove specific text from a cell and than sort cells by month and date
    By Boo123 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-10-2014, 05:05 PM
  2. Calculating product life by month
    By stealabase09 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-28-2014, 12:37 PM
  3. [SOLVED] Need Help: Need to Assign Text to Date Specific Month
    By dooredge in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-27-2013, 04:00 PM
  4. Replies: 3
    Last Post: 02-16-2013, 06:10 PM
  5. Sum Values Based on Month and Product
    By akbar in forum Excel General
    Replies: 16
    Last Post: 08-08-2012, 02:10 PM
  6. [SOLVED] product sum per month per customer
    By Pete Petersen in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-04-2005, 01:06 PM
  7. [SOLVED] Sm Product a Calendar Month Range?
    By John in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-03-2005, 05:06 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