Thread: countif and AND (xl2003 sp2)

1. 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. 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. 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. 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. 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. 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. 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. 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. 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))

10. 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. 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
•