+ Reply to Thread
Results 1 to 5 of 5

Issue with sumproduct/ countifs in regards finding certain words in date range

  1. #1
    Registered User
    Join Date
    06-03-2013
    Location
    US
    MS-Off Ver
    Excel 2016
    Posts
    41

    Issue with sumproduct/ countifs in regards finding certain words in date range

    hello,

    i need to count how many times two different products show up in date ranges.

    one issue: one of my products i need to search for any product with word pea

    second issue: my formula results in zeros when i know i have 8.

    here my formula: =SUMPRODUCT(--($E:$E>=A2),--($E:$E<=B2),--($D:$D="corn"or"pea"))

    here is some data to work with:
    Capture.JPG

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Issue with sumproduct/ countifs in regards finding certain words in date range

    One way...

    =SUM(COUNTIFS(D:D,{"Corn","*Pea*"},E:E,">="&A2,E:E,"<="&B2))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    06-03-2013
    Location
    US
    MS-Off Ver
    Excel 2016
    Posts
    41

    Re: Issue with sumproduct/ countifs in regards finding certain words in date range

    It almost work! it counting 6 more then what my data shows. there are no other products that are close to pea or corn. thoughts?

  4. #4
    Registered User
    Join Date
    06-03-2013
    Location
    US
    MS-Off Ver
    Excel 2016
    Posts
    41

    Re: Issue with sumproduct/ countifs in regards finding certain words in date range

    never mind. it did work. it was pulling a different date range. thank you for your help.!

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Issue with sumproduct/ countifs in regards finding certain words in date range

    Good deal. Thanks for the feedback!

+ 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] COUNTIFS Issue w/multiple date conditions
    By brulottej in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-28-2016, 07:39 PM
  2. [SOLVED] CountIfs within date and sumproduct
    By D_N_L in forum Excel General
    Replies: 5
    Last Post: 06-30-2014, 09:58 AM
  3. [SOLVED] Finding similar words to a target word in a large Excel range.
    By staggers47 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-09-2013, 11:09 AM
  4. [SOLVED] Sumproduct as Countifs issue
    By jake.masters in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-12-2012, 10:49 AM
  5. [SOLVED] SUMPRODUCT/COUNTIFS With 3 Variables Including 1 Date
    By Bellio3105 in forum Excel General
    Replies: 4
    Last Post: 10-04-2012, 05:42 PM
  6. Replies: 8
    Last Post: 05-14-2012, 02:44 PM
  7. Vba: Using Evaluate Sumproduct / Countifs combination for Date
    By vidyuthrajesh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-29-2012, 07:44 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