Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple Criteria (97 SR2 )

    I would like to define a countif statement based on three different criteria, but I'm not quite sure how it might be done. I want to acheive the following:

    Countif (RANGE1) = A, and (RANGE2) = B and (RANGE3) = C

    Is there an easy way to add conditions to the countif formula?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Multiple Criteria (97 SR2 )

    Unless you don't mind creating intermediate criteria columns, you will need an array formula along the lines of

    =SUM((RANGE1=A)*(RANGE2=[img]/forums/images/smilies/cool.gif[/img]*(RANGE3=C))

    for an "AND" join or

    =SUM((RANGE1=A)+(RANGE2=[img]/forums/images/smilies/cool.gif[/img]+(RANGE3=C))

    for an "OR" join
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multiple Criteria (97 SR2 )

    I prefer the longer array formula:
    =COUNT(IF((RANGE1=A)*(RANGE2=[img]/forums/images/smilies/cool.gif[/img]*(RANGE3=C),Range1))
    Since it explicitly says COUNT, it makes it easier to understand later and not have to think about it. I find, in my old age, that my mind is not as clear and I would look later and think that this was a SUM and not a COUNT.

    Also makes it easier to modify to:
    =Average(IF((RANGE1=A)*(RANGE2=[img]/forums/images/smilies/cool.gif[/img]*(RANGE3=C),Range1))
    =Min(IF((RANGE1=A)*(RANGE2=[img]/forums/images/smilies/cool.gif[/img]*(RANGE3=C),Range1))
    Etc for other functions.
    Change "*" to "+" to go from AND to OR

    Steve

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Multiple Criteria (97 SR2 )

    Excellent points, Steve, but apparently you aren't familiar with the principle that job security is directly proportional to spreadsheet obfuscation. <img src=/S/wink.gif border=0 alt=wink width=15 height=15> <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multiple Criteria (97 SR2 )

    I agree with that assesment but only to a point.

    My concern was only for my clarity. I find that the majority of people I deal with have NO idea what ARRAY formulas are or what they do. So I want them to be as clear to me as possible.

    And for me over 75% of my spreadsheets are for me and I might let others have read only access. Most of them are confused by even simple tricks. Some of the "magic" I do with spreadsheets, they don't even realize how difficult it is since they have not even tried to imagine setting up a spreadsheet like that.

    Steve

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Multiple Criteria (97 SR2 )

    I read your array formula advice a while back, and I'm taking it. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multiple Criteria (97 SR2 )

    Drk

    Apart from array formulae, there are the much neglected D (for Database) functions. If you are dealing with a database and your data is structured accordingly they might be more suitable than the array formula approach. In your case DCOUNT (or DCOUNTA) are the appropriate functions. Go to th efunction wizard and select tteh database category and see what is available and check the help files for each function if you need clarification.

    I attach a very simplistic example.

    Andrew
    Attached Files Attached Files

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Multiple Criteria (97 SR2 )

    Just curious Andrew; it seems to me that the "Dfunctions" are Array formulas in disguise (OK, I'm not being technical here!) and take as long to calculate as Array formulas. Any thoughts?
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multiple Criteria (97 SR2 )

    Some editiorial OPINIONS of Mine. (some may disagree):

    Dfunctions require the criteria to be setup and taking up space in the worksheet.

    If you have 1 primary criterion that you play with and have many results based on that one criterion, then I think that the DFunctions are a better way to go. When I think of a single criteria it might contain multiple fields: All "John"s from "PA", who bought items in "2002". Change the criteria and all the calcs change.

    If you want to use MULTIPLE criteria, I think it is easier to use the ARRAY Formulas. you want items from 2002, 2001, 2000, etc calculated and you want each state,etc. You could do it with all the different possible criteria ranges created, but it just seems "neater" to use ARRAYS and reference some cells and not use the "intermediate" criteria.

    Array formulas seem to me to be more akin to using the TABLE (one-way or two-way) feature. Where you set the criteria and have the items in the column and row "headings" placed in the criteria and calculate the sum, avg or whatever on MANY different scenarios at once.

    I have found that ARRAYS calculate faster than the tables and I think they are easier to work with. Tables are tough to modify and are to me less intuitive.

    Always remember. There are usually at least 3 ways to do ANYTHING in excel, and everyone has their own preferences.

    Steve

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multiple Criteria (97 SR2 )

    John,

    I was not suggesting that the Dfunctions were in any superior to Array formulas, just pointing them out as an option. I have not really done any comparisons on the calculation speeds involved. Without knowing the nature of Drk's workbook, I cannot know which would be best in his circumstances, but as Steve suggests they both have their uses, as do pivot tables.

    Andrew

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Criteria (97 SR2 )

    First of all, thanks millions to everyone who has contributed to this thread!

    I'm working with Johns initial solution of Array Formulas, and as pointed out earlier in this thread, Arrays have their benefits, thus, the Arrays are working better for me.

    My workbook contains a varying number of records, all structured in a standard table layout, where each column contains a very specific type of information. The arrays outlined above allow me to gather statistics on three primary areas: Product, Area of Product, and Specific Issue. The 'Categorization' of calls as I have built it, allows for duplication across some specific issues, hence my need for a formula which will allow me to define the product, then area, then issue. (Andrew knows what I'm talking about here a bit more than everyone else... <img src=/S/wink.gif border=0 alt=wink width=15 height=15>) I have used Excel to build a CRM, and these formulas allow me to report on the data retained by this CRM. Each array is used to calculate different criteria. (When it's said and done, there are over 700 array formulas within the 'Reporting' worksheet, all working to pull specific data as it relates to the calls.)

    I hope this clarifies my application of the Arrays, and provides insight to those who might come across this in the future! Thanks again everyone!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Criteria (97 SR2 )

    This is great John, it's working quite well!

    Question though, I'm a bit unfamiliar with Arrays and why they work, but can you think of something that will allow me to specify several criteria, and return only the results that DO NOT match those criteria?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Multiple Criteria (97 SR2 )

    Drk, you might want to post a sample to be sure we understand exactly what you want, but in general terms, re your original post, the following will work:

    =SUM((RANGE1<>A)*(RANGE2<>[img]/forums/images/smilies/cool.gif[/img]*(RANGE3<>C))

    BTW, I hope you discovered that you can refer to other cells which hold the criteria, like this quick and dirty formula in cell B82 I just tested:

    <table border=1><td></td><td align=center>B</td><td align=center>C</td><td align=center valign=bottom>81</td><td valign=bottom>CA</td><td align=right valign=bottom>65</td><td align=center valign=bottom>82</td><td align=right valign=bottom>=SUM((H4:H55<>$B$81)*(D455<C81))</td><td align=right valign=bottom></td></table>(This returns the count of state fees that are less than 65 and aren't California.)

    This is handy for replicating the formulas and modifying the criteria.
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Criteria (97 SR2 )

    Thanks John, yes, I had discovered that 'dirty little trick' as you put it, it's saved me GOBS of time!

    The problem I'm running into is that I've recently re-defined the entire categorization for each call, so in one sub table, I have to specify to return only results that fall before a specific date, while in another, I return results that only fall after. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Thanks for all your help, this is working perfectly! <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  15. #15
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Criteria (97 SR2 )

    Last Question, then I think I will have worked these particular arrays to death. [img]/forums/images/smilies/smile.gif[/img]

    Is there a way to match a portion of criteria? This would count the cell if it contains a string.

    Thanks again John!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •