Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concatenating Functions (Excell 2003, Off Prof 2003, Win 2000)

    Don't know if this is possible, but it'd be nice if it was...

    On a grid of data, I can do COUNTIF(Range, >3) to get a count of, well, all cells in the range greater than 3. I have an additional column with either "Manager" or "Deputy/Director" and I want to modify this formula to do a test on that column to only count cells in the COUNTIF range if cells in the column equal Manager OR Director. is this possible?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenating Functions (Excell 2003, Off Prof 2003, Win 2000)

    Do you mean that you want a COUNTIF with multiple criteria? This can be realized by using SUMPRODUCT. For example, the formula
    <code>
    =SUMPRODUCT((A2:A100>3)*(B2:B100="Manager"))
    </code>
    will count the number of rows in the range 2-100 where the value in column A is greater than 3 *and* the corresponding value in column B equals "Manager".

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating Functions (Excell 2003, Off Prof

    hmm - well I tried that and it seems to not work. I don't want to add the data in the cells but just count all cells with data in specific ranges. I am analyzing results from a survey with weighted questions and (bad = 1, not so bad = 2, neutral = 3, ok = 4, great = 5) so the 'value' indicates the selected choice. there's a formula for deriving the average choice based on a sum of the scores but I need to extract counts based on a range of responses. I can already do all this, but I wanted to check my results so want to do the same thing differently.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenating Functions (Excell 2003, Off Prof

    The formula I posted does count cells, even though it uses SUMPRODUCT. Could you try to explain more clearly what you want to accomplish?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating Functions (Excell 2003, Off Prof

    Whoops - sorry - late in the day... you are correct of course. I thought I had checked but Excel loves to re-write your ranges when you move cells around. I reset the ranges and the numbers all add up now.

    Thanks!

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Concatenating Functions (Excell 2003, Off Prof 2003, Win 2000)

    Hello Hans
    Is it possible to incorporate an OR function into this formula such that the row would be counted if column B contained either "Manager" or "Director"? I have been playing with this problem sans success.

    T.I.A.
    Regards
    Don

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenating Functions (Excell 2003, Off Prof 2003, Win 2000)

    You can add the results of two COUNTIF formulas:

    =COUNTIF(B2:B100,"Manager")+COUNTIF(B2:B100,"Direc tor")

    And with multiple conditions, you can add the results of two SUMPRODUCT formulas:

    =SUMPRODUCT((A2:A100>3)*(B2:B100="Manager"))+SUMPR ODUCT((A2:A100>3)*(B2:B100="Director"))

    or you can add within the SUMPRODUCT formula:

    =SUMPRODUCT((A2:A100>3)*((B2:B100="Manager")+(B2:B 100="Director")))

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Concatenating Functions (Excell 2003, Off Prof 2003, Win 2000)

    Thank you Hans <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards
    Don

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Concatenating Functions (Excell 2003, Off Prof

    This should also work:
    =SUMPRODUCT((A2:A100>3)*(B2:B100={"Manager","Direc tor"}))
    FWIW
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Concatenating Functions (Excell 2003, Off Prof

    Thanks Rory

    I was surprised to find the squiggly brackets usable with a standard "Enter" as opposed to the "Ctrl + Shift + Enter" I associate with array functions. I believe that I now understand.
    Regards
    Don

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Concatenating Functions (Excell 2003, Off Prof

    Don,
    I should have added a caveat that you should only use that syntax once within a SUMPRODUCT formula or you will get incorrect results. If you need more than one OR condition, use the addition syntax that Hans used.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Concatenating Functions (Excell 2003, Off Prof

    Thank you for the additional info Rory.
    Regards
    Don

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Concatenating Functions (Excell 2003, Off Prof

    Nice pun! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Concatenating Functions (Excell 2003, Off Prof

    None intended.
    Regards
    Don

Posting Permissions

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