Results 1 to 11 of 11
Thread: countif and AND (xl2003 sp2)

20060329, 23:01 #1
 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 nonzero amount.
An appropriate formula, please, preferably by a method not involving a separate column for the AND bit of the function?
Thanks in advance.

20060329, 23:05 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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))

20060329, 23:10 #3
 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 resourcehungry (and how do you know?)

20060329, 23:18 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: countif and AND (xl2003 sp2)
I'm not sure. Array formulas are reputedly resourcehungry, 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.

20060329, 23:31 #5
 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.

20060329, 23:46 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20060330, 00:08 #7
 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

20060401, 16:24 #8
 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....

20060402, 09:27 #9
 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 DataListCreate 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

20060402, 22:25 #10
 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.

20060402, 22:26 #11
 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