+ Reply to Thread
Results 1 to 2 of 2

Sumproduct by month that excludes text

  1. #1
    Registered User
    Join Date
    11-22-2016
    Location
    Pennsylvania
    MS-Off Ver
    2010
    Posts
    1

    Question Sumproduct by month that excludes text

    Hello Everyone!

    I have the following problem...

    Sheet1: Column A has quantities of items (#'s), Column B has one word written text descriptions, column C has dates... example:

    (A)Quantity----(B)Cause of defect----(C)Dates
    ------------- -------------------- ---------
    1........................Defective..............1/23/16
    16......................Damaged..............2/15/16
    5...........................NWR..................2/16/16

    I need a formula to add the quantities in column A that fall into a specific month. I found that with this... =SUMPRODUCT((MONTH('Sheet1'!C$1:C$3)=2)*'Sheet1'!A$1:A$3)
    and I get the desired total quantity for the month, in this case Feb, which is great *(in this example I would get 21 as my result). However, I want to add the quantity in Feb that does not include rows that have a defect "NWR" (Hoping in this case to get a result of 16). This is only an example, my spreadsheet has hundreds of entries.

    I was thinking of adding, ('Sheet1'!B$1:B$3<>"NWR") in the mix somehow, but no matter how I add it I either get 0 for my result or "#Value". Any help is much appreciated. Thank you!

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Sumproduct by month that excludes text

    This works for me:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Note that SUMPRODUCT can be a bit clunky on large datasets. You could use SUMIFS to do similar.

    BSB

+ 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] Randomly generate dates of the current month and excludes weekends
    By jaycee66 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-02-2016, 10:10 PM
  2. [SOLVED] SUMPRODUCT for information of MONTH and TEXT (2 criteria)
    By mallory.chui in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-06-2015, 05:31 AM
  3. SUMPRODUCT + TEXT (Month 27th) - Help amending this formula?
    By domgilberto in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-22-2014, 06:18 PM
  4. [SOLVED] Sumproduct/text/month
    By sick stigma in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-28-2012, 03:39 PM
  5. [SOLVED] Sumproduct, Month, Ignore text
    By jennyaccord in forum Excel General
    Replies: 7
    Last Post: 06-14-2012, 06:30 AM
  6. SumProduct error when using Year , Month and text criteria
    By jsrobin in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-11-2008, 10:56 PM
  7. SUMPRODUCT of month AND text
    By shahidkhaki in forum Excel General
    Replies: 1
    Last Post: 08-19-2007, 05:28 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