countif and AND (xl2003 sp2)

Topic

New Reply

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.

Reply | Quote

Replies

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

=SUMPRODUCT((A2:A22=1)*(B2:B220))

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

=SUM((A2:A22=1)*(B2:B220))

Reply | Quote

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?)

Reply | Quote

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.

Reply | Quote

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)*(amount0))

It returns a #NUM error.

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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 for example) or even do a search on “Sluggish” and I think you will find other threads…]

Steve

Reply | Quote

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

Reply | Quote