Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    countif and AND (xl2003 sp2)

    Sorry, addled brain.
    The attached sample has two columns per record, type and amount
    The problem is to count the number of records where each 'type' is associated with a non-zero amount.
    An appropriate formula, please, preferably by a method not involving a separate column for the AND bit of the function?
    Thanks in advance.

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

    Re: countif and AND (xl2003 sp2)

    I assume that you meant <>0 in all three cases. A possible formula for type = 1:

    =SUMPRODUCT((A2:A22=1)*(B2:B22<>0))

    or the array formula (confirm with Ctrl+Shift+Enter)

    =SUM((A2:A22=1)*(B2:B22<>0))

  3. #3
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif and AND (xl2003 sp2)

    Thanks, Hans, not even enough time to go and put the kettle on!
    As the workbook concerned will have many, many thousands of such records, which do you recommend as less resource-hungry (and how do you know?)

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

    Re: countif and AND (xl2003 sp2)

    I'm not sure. Array formulas are reputedly resource-hungry, but SumProduct works like an array formula too even though you don't have to confirm it with Ctrl+Shift+Enter. Perhaps someone else can shed some light on this.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif and AND (xl2003 sp2)

    Sorry, Hans, neither of these works for me, neither by typing as the range reference the whole column eg H:H nor the name of the whole column, which is

    type

    Similarly for the amount column

    Here I've pasted from the formula bar
    =SUMPRODUCT((type=1)*(amount<>0))

    It returns a #NUM error.

    What's wrong? Both columns are formatted as numbers.

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

    Re: countif and AND (xl2003 sp2)

    Array type formulas don't work with entire columns - see Description of the limitations for working with arrays in Excel 2000, Excel 2002, and Excel 2003. You'll have to define "finite" ranges.

  7. #7
    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: countif and AND (xl2003 sp2)

    I have seen sluggishness issues with large arrays and many of them. This is due to large amount of calculations in each one. I imagins that SUMPRoDUCT is slightly better but will still have issues.

    If the sluggish performance is noted, it might be time to write code to create your table of values. You can optimize the code to eliminate many of the multiple passes thru the data in your code so even having the code run at the worksheet change event could be faster than all those arrays...

    [The concept has been discussed before (see <post:=315,619>post 315,619</post:> for example) or even do a search on "Sluggish" and I think you will find other threads...]

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif and AND (xl2003 sp2)

    Thank you both very much, sorry about the late response.
    We live and learn.
    The great thing is learning....

  9. #9
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif and AND (xl2003 sp2)

    Since you are on Excel 2003, select the data area (including the headers) and run Data|List|Create List. All formulas referring to (parts of) the list will adjust automatically to changes to it.

    A tad bit faster multiconditional counting can be effected with:

    =SUMPRODUCT(--($A$2:$A$22=1),1-($B$2:$B$22=0))
    Microsoft MVP - Excel

  10. #10
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif and AND (xl2003 sp2)

    Aladin, please explain the en rules aka hyphens aka minus signs in your formula?
    =SUMPRODUCT(--($A$2:$A$22=1),1-($B$2:$B$22=0)) ??
    Concerning efficient use of resources and optimised code, commended by Steve, here's a workbook which I think describes and illustrates the problem (aka opportunity.)
    A line of code telling excel to not add a new row if the money value of that described is zero would enable a straight countif to continue to tell the truth.
    Thanks in advance, yet again.

  11. #11
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif and AND (xl2003 sp2)

    Aladin,
    The bit about excel 2003 is true but not suitable, as I can't guarantee that other users have 2003. Nice idea, though.
    JRR

Posting Permissions

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