# Array Formula Question?

#### jdowski

##### Board Regular
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.

Joe
Woodbury, CT. USA.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

##### MrExcel MVP
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.

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.

##### MrExcel MVP
Here's the array formula solution as requested...
Book2
ABCDEFGHIJ
2McDonalds1000McDonalds23
3McDonalds2500
4McDonalds1500
5McDonalds1800
6McDonalds1700
7
Sheet1

#### jdowski

##### Board Regular
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.

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

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

#### Mark W.

##### MrExcel MVP
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

##### Board Regular
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

##### Board Regular
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

Replies
1
Views
229
Replies
7
Views
640
Replies
2
Views
354
Replies
12
Views
351
Replies
6
Views
112

1,181,539
Messages
5,930,499
Members
436,741
Latest member
buckeyerich

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back