Array Formula Question?

jdowski

Board Regular
Joined
Apr 21, 2002
Messages
235
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Here's the array formula solution as requested...
Book2
ABCDEFGHIJ
1BusinessPerformingNonperformingPerformingNonperforming
2McDonalds1000McDonalds23
3McDonalds2500
4McDonalds1500
5McDonalds1800
6McDonalds1700
7
Sheet1
 

jdowski

Board Regular
Joined
Apr 21, 2002
Messages
235
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654

ADVERTISEMENT

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
Joined
Apr 21, 2002
Messages
235
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
Joined
Apr 21, 2002
Messages
235
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
 

Forum statistics

Threads
1,143,923
Messages
5,721,557
Members
422,370
Latest member
A Nonomus

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top