+ Reply to Thread
Results 1 to 7 of 7

Forumlas with multiple conditions

  1. #1
    Registered User
    Join Date
    07-01-2014
    Location
    UK
    MS-Off Ver
    MS Office Professional Plus 2013 on Win 7
    Posts
    14

    Forumlas with multiple conditions

    The concept is very simple (and I can do it in SQL) but having a lot of problems trying to create the formula in excel.

    I need to count the number of rows in another sheet where the status is certain values and one of the date fields falls between certain dates.

    I can do each part individually, but can not create a combined formula and I'm not sure if this is something I am doing wrong now or something that can not be done.

    My formula for counting the number of rows based on a date (this checks another sheet in the book but the principal is the same)
    =COUNTIFS('TEST DATA'!A:A,">"&B19,'TEST DATA'!A:A,"<="&B18)

    My formula for counting the number of occurances of particular statues.
    =COUNTIFS('TEST DATA2'!F:F,"On Hold Customer")+COUNTIFS('TEST DATA2'!F:F,"On Hold Other")


    so in pseudo code terms
    IF the date column on page TEST DATA2 is > one date value and <= another date value
    AND
    Order Status coumn on TEST DATA2 is one of a number of values
    COUNT THIS ROW.


    Can this be done?


    Thanks

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,543

    Re: Forumlas with multiple conditions

    Hi eSmith and welcome to the forum,

    We need to know what version of Excel you are using. You can add that to your Profile page. The answers we give may vary based on the version you are using. See
    http://office.microsoft.com/en-us/ex...010342341.aspx
    for why and see if it helps.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    07-01-2014
    Location
    UK
    MS-Off Ver
    MS Office Professional Plus 2013 on Win 7
    Posts
    14

    Re: Forumlas with multiple conditions

    Hi Marvin, I'm using MS Office Professional Plus 2013, but will update my profile page.

    Thanks

  4. #4
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Forumlas with multiple conditions

    You almost had it. I think this should work:
    =COUNTIFS('TEST DATA'!A:A,">"&B19,'TEST DATA'!A:A,"<="&B18,'TEST DATA2'!F:F,"On Hold Customer")+COUNTIFS('TEST DATA'!A:A,">"&B19,'TEST DATA'!A:A,"<="&B18,'TEST DATA2'!F:F,"On Hold Other")

    It may be able to be simplified if your only instances of strings that begin with "On Hold" are those two:
    =COUNTIFS('TEST DATA'!A:A,">"&B19,'TEST DATA'!A:A,"<="&B18,'TEST DATA2'!F:F,"On Hold*")
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,543

    Re: Forumlas with multiple conditions

    Hi,

    If you have the latest version of Excel you can do "Between" dates in a pivot table. I've created a simple table of dates, sku and amt. Then I did a pivot table with the data and selected Between Dates and only a few of the SKUs. See if this helps with your problem. Learn more about Pivots?
    http://www.contextures.com/excel-piv...ters-date.html
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-01-2014
    Location
    UK
    MS-Off Ver
    MS Office Professional Plus 2013 on Win 7
    Posts
    14

    Re: Forumlas with multiple conditions

    Thank you, thank you, thank you!!!

    You have literally saved my sanity on this.



    Quote Originally Posted by Pauleyb View Post
    You almost had it. I think this should work:
    =COUNTIFS('TEST DATA'!A:A,">"&B19,'TEST DATA'!A:A,"<="&B18,'TEST DATA2'!F:F,"On Hold Customer")+COUNTIFS('TEST DATA'!A:A,">"&B19,'TEST DATA'!A:A,"<="&B18,'TEST DATA2'!F:F,"On Hold Other")

    It may be able to be simplified if your only instances of strings that begin with "On Hold" are those two:
    =COUNTIFS('TEST DATA'!A:A,">"&B19,'TEST DATA'!A:A,"<="&B18,'TEST DATA2'!F:F,"On Hold*")

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Forumlas with multiple conditions

    this seems to work
    =SUM(COUNTIFS('test data'!A:A,">"&B19,'test data'!A:A,"<="&B18,'test data2'!F:F,{"On Hold Customer","On Hold Other"}))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ 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] Vlookup - Country conditions (Multiple conditions)
    By dluhut in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-26-2013, 11:42 AM
  2. [SOLVED] Doing multiple forumlas in excel
    By BrianB77 in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 01-06-2013, 11:27 PM
  3. Need Forumlas for extracting from multiple columns/rows
    By icanspeakwhale in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2011, 01:48 PM
  4. Multiple separate if forumlas in one cell
    By joseclar in forum Excel General
    Replies: 12
    Last Post: 05-05-2009, 02:48 PM
  5. How to multiple conditions to validate more than 2 conditions to .
    By Bhuvana Govind in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-28-2005, 04:06 PM

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