Another Array Formula Question.....oops!

[jdowski]

Hi All,

This post is an addition to an earlier post from today. I have 4 columns of data;
Column A is a business name where there can be multiple entries for the same business.
Columns B & C are currency fields. If there is an currency entry for a given business in Col B (Performing)there won't be one for Col C, (Non Performing) and vice versa. There is also a column D,(Availability) which is also a currency field. There can be entries in Col D whether there are entries in Col's B or C.
What I want to do is sum, by business (Col A), the total for Column D grouped by Col's B & C, (Performing & Non Performing).
Here is the array formula that I have tried for getting the totals for Non Performing.
=SUM(if(Business=A6),if(Performing=""),Availability,0),0)
Any suggestions would be welcomed,

Joe
Woodbury, CT. USA.

 

[Dave Patton]

On 2002-10-15 15:06, jdowski wrote:
Hi All,

This post is an addition to an earlier post from today. I have 4 columns of data;
Column A is a business name where there can be multiple entries for the same business.
Columns B & C are currency fields. If there is an currency entry for a given business in Col B (Performing)there won't be one for Col C, (Non Performing) and vice versa. There is also a column D,(Availability) which is also a currency field. There can be entries in Col D whether there are entries in Col's B or C.
What I want to do is sum, by business (Col A), the total for Column D grouped by Col's B & C, (Performing & Non Performing).
Here is the array formula that I have tried for getting the totals for Non Performing.
=SUM(if(Business=A6),if(Performing=""),Availability,0),0)
Any suggestions would be welcomed,

Joe
Woodbury, CT. USA.

You can extend either the Array or Sumproduct
formulas

Array enter

=SUM(IF(($A$2:$A$6=$E4)*ISNUMBER($B$2:$B$6),$D$2:$D$6))

or just enter

=SUMPRODUCT(($A$2:$A$20=E3)*($C$2:$C$20>0),$D$2:$D$20)

Aladin recently provided an excellent commentary on SumProduct.

 

[Mark W.]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Business	Performing	Nonperforming	Availability			Performing	Nonperforming
2	McDonalds	1000		$2,471		McDonalds	2	3
3	McDonalds		2500	$1,502		McDonalds	$5,172	$5,726
4	McDonalds	1500		$2,701
5	McDonalds		1800	$1,283
6	McDonalds		1700	$2,941
7
8
9
Sheet1

 

[Aladin Akyurek]

On 2002-10-15 15:06, jdowski wrote:
Hi All,

This post is an addition to an earlier post from today. I have 4 columns of data;
Column A is a business name where there can be multiple entries for the same business.
Columns B & C are currency fields. If there is an currency entry for a given business in Col B (Performing)there won't be one for Col C, (Non Performing) and vice versa. There is also a column D,(Availability) which is also a currency field. There can be entries in Col D whether there are entries in Col's B or C.
What I want to do is sum, by business (Col A), the total for Column D grouped by Col's B & C, (Performing & Non Performing).
Here is the array formula that I have tried for getting the totals for Non Performing.
=SUM(if(Business=A6),if(Performing=""),Availability,0),0)
Any suggestions would be welcomed,

Joe
Woodbury, CT. USA.

=SUMPRODUCT(Business-Range=A6)*(Performing-range>0),Availability-Range))

I leave the other to you.

The above formula operates on arrays just like an array-entered formula does. The former executes slightly faster than the latter.

See

http://www.mrexcel.com/wwwboard/messages/8961.html


http://www.mrexcel.com/board/viewtopic.php?topic=25380&forum=2
This message was edited by Aladin Akyurek on 2002-10-15 15:21

 

[Mark W.]

>The former executes slightly faster than the latter.

...how so?