Array Formula Question?

[jdowski]

Hi,
I have three colums of information:
The first column has business names and has multiple entries of businesses.
The next two columns are currency fields, I'll call them "performing" and "non-performing". For each line entry you may only have a value in either performing or non-performing.
So, if I have 5 entries of say McDonalds, 2 of them will have values in the performing column and 3 will have values in the non performing column. I am trying to write an array formula that that in one cell will give me a count of non performing McDonalds loans and another that will give me the count of performing McDonalds loans.

Please help...

Joe
Woodbury, CT. USA.

 

[Aladin Akyurek]

On 2002-10-15 12:00, jdowski wrote:
Hi,
I have three colums of information:
The first column has business names and has multiple entries of businesses.
The next two columns are currency fields, I'll call them "performing" and "non-performing". For each line entry you may only have a value in either performing or non-performing.
So, if I have 5 entries of say McDonalds, 2 of them will have values in the performing column and 3 will have values in the non performing column. I am trying to write an array formula that that in one cell will give me a count of non performing McDonalds loans and another that will give me the count of performing McDonalds loans.

Please help...

Joe
Woodbury, CT. USA.

Let A2:A20 house the names, B2:B20 "performing loans", and C2:C10 "non-performing loans".

=SUMPRODUCT(($A$2:$A$20=E2)*($B$2:$B$20>0))

will give you desired count regarding the business name in E2 and the performing loans, and...

=SUMPRODUCT(($A$2:$A$20=E2)*($C$2:$C$20>0))

the count regarding the non-performing loans.

I took the loans as values > 0.

The above formulas are entered normally.
This message was edited by Aladin Akyurek on 2002-10-15 12:10

 

[Mark W.]

Here's the array formula solution as requested...

Book2
	A	B	C	D	E	F	G	H	I	J
1	Business	Performing	Nonperforming			Performing	Nonperforming
2	McDonalds	1000			McDonalds	2	3
3	McDonalds		2500
4	McDonalds	1500
5	McDonalds		1800
6	McDonalds		1700
7
Sheet1

 

[jdowski]

On 2002-10-15 12:08, Aladin Akyurek wrote:

On 2002-10-15 12:00, jdowski wrote:
Hi,
I have three colums of information:
The first column has business names and has multiple entries of businesses.
The next two columns are currency fields, I'll call them "performing" and "non-performing". For each line entry you may only have a value in either performing or non-performing.
So, if I have 5 entries of say McDonalds, 2 of them will have values in the performing column and 3 will have values in the non performing column. I am trying to write an array formula that that in one cell will give me a count of non performing McDonalds loans and another that will give me the count of performing McDonalds loans.

Please help...

Joe
Woodbury, CT. USA.

Let A2:A20 house the names, B2:B20 "performing loans", and C2:C10 "non-performing loans".

=SUMPRODUCT(($A$2:$A$20=E2)*($B$2:$B$20>0))

will give you desired count regarding the business name in E2 and the performing loans, and...

=SUMPRODUCT(($A$2:$A$20=E2)*($C$2:$C$20>0))

the count regarding the non-performing loans.

I took the loans as values > 0.

The above formulas are entered normally.
This message was edited by Aladin Akyurek on 2002-10-15 12:10

Aladin,

You have bailed me out again!!!
Thanks very much!!

 

[Mark W.]

Joseph, keep in mind that Aladin's solution is not an array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".

 

[jdowski]

Thank you Mark W. !
I can't say how great it is to be able to get my questions answered in a forum such as this. My abilities in Excel have increased greatly in the last several months due in no small part to this web site.
Bravo Mr. Excel !!

 

[jdowski]

On 2002-10-15 12:21, Mark W. wrote:
Joseph, keep in mind that Aladin's solution is not an array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".

So much the better Mark, that Aladin's formulas are not arrays as I will need about 65 cells on one sheet to do that calculation.
Thanks again,

Joe