+ Reply to Thread
Results 1 to 4 of 4

sumproduct month and other cirteria

  1. #1
    Forum Contributor
    Join Date
    11-18-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    132

    sumproduct month and other cirteria

    HI I have the following formula which is counting all the dates in column B that match the month number in cell I2

    =SUMPRODUCT(--(MONTH('Tracker'!$B$3:$B$5000)=$I$1),--('Tracker'!$B$3:$B$5000<>""))

    However I need to add another criteria to the formula so that it looks for a specific text in another column as well. I have tried the following but neither works and I don't know enough about SUMPRODUCT to fix it, any ideas?

    =SUMPRODUCT(--(MONTH('Tracker'!$B$3:$B$5000)=$I$1),--('Tracker'!$B$3:$B$5000<>""),--(Tracker'!$D$2:$D$5000="no answer"))

    =SUMPRODUCT(--(MONTH('Tracker'!$B$3:$B$5000)=$I$1),--('Tracker'!$B$3:$B$5000<>"")*(Tracker'!$D$2:$D$5000="no answer"))

    Thanks

    J

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,418

    Re: sumproduct month and other cirteria

    Needs to be the same range size so $D$2 should be $D$3
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    11-18-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    132

    Re: sumproduct month and other cirteria

    D'oh, sometimes it needs another pair of eyes to see the smallest mistakes, have corrected the ranges and it works fine now.
    Thanks

    J

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,418

    Re: sumproduct month and other cirteria

    You're welcome. Thanks for the rep.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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] Month Over Month Headcount with SUMPRODUCT
    By kdalyveris in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-05-2020, 04:55 PM
  2. [SOLVED] display information based on two cirteria
    By kobiashi in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-20-2018, 08:57 PM
  3. [SOLVED] identify duplicate in a range or overlapping with cirteria
    By shuaibfarouk in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-01-2017, 05:32 AM
  4. vlookup with 2 cirteria
    By anwitha in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-09-2015, 03:00 AM
  5. [SOLVED] COUNTIF in an array with two cirteria (one of which is a date)
    By costgeek in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-30-2015, 12:27 PM
  6. VBA to populate to specific cells based on cirteria
    By huard in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2014, 07:41 AM
  7. Countif Multiple Cirteria
    By Danielle 76 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 09-06-2005, 07:05 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