+ Reply to Thread
Results 1 to 11 of 11

Multiple Criteria's

  1. #1
    Registered User
    Join Date
    11-19-2010
    Location
    USA NY
    MS-Off Ver
    Excel 2007
    Posts
    34

    Multiple Criteria's

    Hello guys-

    I have these two formulas below that I wish to add 3 criterias to…

    Please Login or Register  to view this content.
    Criteria =

    G$1:$G$10000= "4-more time"
    G$1:$G$10000= "3-recall"
    G$1:$G$10000= "6-follow up"

    Please Login or Register  to view this content.
    Criteria =

    G:G= "4-more time"
    G:G= "3-recall"
    G:G= "6-follow up"

    Any help will be greatly appreciated. Thanks in advance for your assistance!
    Last edited by rosarionyc; 11-24-2010 at 02:44 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Multiple Criteria's

    You mean you want to add if G1:G10000 is either of those conditions?

    If so:

    Please Login or Register  to view this content.
    and

    Please Login or Register  to view this content.
    Note: Countifs, Sumifs, Averageifs don't take OR condtions....
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    11-19-2010
    Location
    USA NY
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: Multiple Criteria's

    Quote Originally Posted by NBVC View Post
    You mean you want to add if G1:G10000 is either of those conditions?

    If so:

    Please Login or Register  to view this content.
    and

    Please Login or Register  to view this content.
    Note: Countifs, Sumifs, Averageifs don't take OR condtions....
    Hello and thank you. The 1st formula seem to work but i am not yet sure of it because there is a 2nd formula in which i need to alter as well. I tried to follow the format as described in the 1st formula but i got an error messege, this is why i am posting the 2nd formula for your review. Thanks!

    Please Login or Register  to view this content.
    Additional Criteria =

    '3-Service Calculator'!$G$1:$G$10000="8-high part"
    Last edited by rosarionyc; 11-24-2010 at 11:51 AM.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Multiple Criteria's

    That criteria looks like the one in the formula already. Is that the right one?

  5. #5
    Registered User
    Join Date
    11-19-2010
    Location
    USA NY
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: Multiple Criteria's

    Quote Originally Posted by NBVC View Post
    That criteria looks like the one in the formula already. Is that the right one?
    I adjusted it. The formula has 7-part call, the addition is 8-high part.

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Multiple Criteria's

    Try:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    11-19-2010
    Location
    USA NY
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: Multiple Criteria's

    Quote Originally Posted by NBVC View Post
    Try:

    Please Login or Register  to view this content.
    Thank you, that worked perfectly. Currently i am looking into the 2nd formula you provided me. Seems to have given me a lesser SUM than the original formula; BRB soon.

  8. #8
    Registered User
    Join Date
    11-19-2010
    Location
    USA NY
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: Multiple Criteria's

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

    The formula you created is a little over my head, so I will explain the one that I have. Its reads as follows…
    Criteria
    If G:G = 3-service call < plus the additions > 3-recall, 4-more time, 6-follow up
    And
    X:X = 1
    Result
    Go to
    S:S = SUM the average/24

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Multiple Criteria's

    the definition of Average is to SUM Divided by the Count

    So the first SUMPRODUCT() sums, then the second SUMPRODUCT() counts.

    The + signs between the first 5 conditions means OR.. so what it does is check if the G range contains any of those 5 conditions and returns TRUE if they do. Then it checks the X range for 1 and returns TRUE, the 2 results are matched up and multiplied.. any TRUE*TRUE returns 1 and any other combination yields 0 result, and those 1's and 0's are subsequently multiplied by the corresponding values in S range and added together to give total SUM of S range where G range meets the criterias at the same time that X meets the criteria set.

    The second Sumproduct() is the same, except there is no S range to sum and so it just returns a count of the matching condition combinations.

    So the Sum divided by the count should equate to the AVERAGE. Then that average is divided by 24.

  10. #10
    Registered User
    Join Date
    11-19-2010
    Location
    USA NY
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: Multiple Criteria's

    Please Login or Register  to view this content.
    Can you please add this criteria to this

    Criteria = '3-Service Calculator'!$G:$G,"8-high part"

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Multiple Criteria's

    Try:

    Please Login or Register  to view this content.

    Also note, that SUMPRODUCT is less efficient than AVERAGEIFS so try to limit the range sizes to the absolute largest you will need... a range of 1:10000 may cause performance issues if you have many of them.

    Edit: You can possibly also just add 5 different AVERAGEIFS together, one for each Grange value (keeping the X and S ranges in tact in each). and then divide the entire thing by 24
    Last edited by NBVC; 11-24-2010 at 01:32 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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