# Array Formulas

#### pgzr

##### New Member
As a new user of MrExcel.com, I have found posts here quite helpful. Now I'm asking for your assistance.

Using previous Mr. Excel posts I was able to create the following array formula, which works great, as long as I choose one of the criteria choices (i.e. control = Profit or control = Non-Profit). However, I also need to be able to choose all companies regardless of control (i.e., control = either Profit or Non-Profit).

I need to be able do this with at least three of the criteria (control, bedrange, peergrp).

I would greatly appreciate any guidance offered.

{=SUM(IF(type=F\$11,IF(bedrange=F\$12,IF(coun=F\$13,IF(control=F\$14,IF(peergrp=F\$15,IF(HOSP_BASE=F\$16,SUMMARY!\$Q\$2:\$Q\$1309/F\$18)))))))}

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### Dave Patton

##### Well-known Member
Using previous Mr. Excel posts I was able to create the following array formula, which works great, as long as I choose one of the criteria choices (i.e. control = Profit or control = Non-Profit). However, I also need to be able to choose all companies regardless of control (i.e., control = either Profit or Non-Profit).

I need to be able do this with at least three of the criteria (control, bedrange, peergrp).

I would greatly appreciate any guidance offered.

{=SUM(IF(type=F\$11,IF(bedrange=F\$12,IF(coun=F\$13,IF(control=F\$14,IF(peergrp=F\$15,IF(HOSP_BASE=F\$16,SUMMARY!\$Q\$2:\$Q\$1309/F\$18)))))))}

Are each of the areas named ranges?

Consider

=SUMPRODUCT((Type=F\$11)*(berange=F\$12)*(coun=F\$13)*(control=F\$14:G\$14)*(peergrp=F\$15)*(Hosp_Base=F\$16)*(\$Q\$2:\$Q\$10))

edited control criteria to allow range F14:G14

an alternative follows

=SUMPRODUCT((Type=F\$11)*(berange=F\$12)*(coun=F\$13)*(control={"Profit","X"})*(peergrp=F\$15)*(Hosp_Base=F\$16)*(\$Q\$2:\$Q\$10))

Adjust the sheet name and scope of the range to fit your information.

You could search this forum for information on Dynamic Ranges particularly Aladin's suggestions.

Note. Sumproduct does not have to be Array Entered.
This message was edited by Dave Patton on 2002-09-19 08:25

#### pgzr

##### New Member
Dave, thanks for the input.

In answer to your question, I have 6 named ranges that are referred to in my formula.

I just tried the SUMPRODUCT formula you suggested, but it doesn't seem to do the trick, either. I'm wondering if I need to tweak the Criteria that the formula is looking at, rather than the formula.

Let me clarify what I mean. Taking "Control" as an example, the value of cell F14 can be Profit or Non-Profit. My "control" range contains cells with the value of either Profit or Non-Profit.

If I'm asked to report all the Profit data, and I enter "Profit " in F14, my formula works fine. Same goes if I'm asked for Non-Profit data, and I enter "Non-Profit" in F14. However, if I'm asked for all the data, regardless of Profit or Non-Profit, I'm not sure what to put in F14 to tell the formula "I don't care what value is in here, include the data anyway"? Or a way to tell the formula "If F14=All, ignore the control criteria altogether"?

The Control example is the simplest example in my case. I have to do the same thing with the "coun" criteria, which could be one of 88 values, or ALL of those values.

Am I doomed?

Original formula:

{=SUM(IF(type=F\$11,IF(bedrange=F\$12,IF(coun=F\$13,IF(control=F\$14,IF(peergrp=F\$15,IF(HOSP_BASE=F\$16,SUMMARY!\$R\$2:\$R\$1309/F\$18)))))))}

##### MrExcel MVP
On 2002-09-19 10:52, pgzr wrote:
Dave, thanks for the input.

In answer to your question, I have 6 named ranges that are referred to in my formula.

I just tried the SUMPRODUCT formula you suggested, but it doesn't seem to do the trick, either. I'm wondering if I need to tweak the Criteria that the formula is looking at, rather than the formula.

Let me clarify what I mean. Taking "Control" as an example, the value of cell F14 can be Profit or Non-Profit. My "control" range contains cells with the value of either Profit or Non-Profit.

If I'm asked to report all the Profit data, and I enter "Profit " in F14, my formula works fine. Same goes if I'm asked for Non-Profit data, and I enter "Non-Profit" in F14. However, if I'm asked for all the data, regardless of Profit or Non-Profit, I'm not sure what to put in F14 to tell the formula "I don't care what value is in here, include the data anyway"? Or a way to tell the formula "If F14=All, ignore the control criteria altogether"?

The Control example is the simplest example in my case. I have to do the same thing with the "coun" criteria, which could be one of 88 values, or ALL of those values.

Am I doomed?

Original formula:

{=SUM(IF(type=F\$11,IF(bedrange=F\$12,IF(coun=F\$13,IF(control=F\$14,IF(peergrp=F\$15,IF(HOSP_BASE=F\$16,SUMMARY!\$R\$2:\$R\$1309/F\$18)))))))}

Array-enter:

=SUM((type=F\$11)*(bedrange=F\$12)*(coun=F\$13)*(IF(LEN(F\$14),control=F\$14,1)*(peergrp=F\$15)*(HOSP_BASE=F\$16),SUMMARY!\$R\$2:\$R\$1309/F\$18))

Leave F\$14 empty to effect: "ignore the condition for control".

The same logic applies to the coun condition.

#### Dave Patton

##### Well-known Member
On 2002-09-19 10:52, pgzr wrote:
Dave, thanks for the input.

In answer to your question, I have 6 named ranges that are referred to in my formula.

I just tried the SUMPRODUCT formula you suggested, but it doesn't seem to do the trick, either. I'm wondering if I need to tweak the Criteria that the formula is looking at, rather than the formula.

Let me clarify what I mean. Taking "Control" as an example, the value of cell F14 can be Profit or Non-Profit. My "control" range contains cells with the value of either Profit or Non-Profit.

If I'm asked to report all the Profit data, and I enter "Profit " in F14, my formula works fine. Same goes if I'm asked for Non-Profit data, and I enter "Non-Profit" in F14. However, if I'm asked for all the data, regardless of Profit or Non-Profit, I'm not sure what to put in F14 to tell the formula "I don't care what value is in here, include the data anyway"? Or a way to tell the formula "If F14=All, ignore the control criteria altogether"?

The Control example is the simplest example in my case. I have to do the same thing with the "coun" criteria, which could be one of 88 values, or ALL of those values.

Am I doomed?

Original formula:

{=SUM(IF(type=F\$11,IF(bedrange=F\$12,IF(coun=F\$13,IF(control=F\$14,IF(peergrp=F\$15,IF(HOSP_BASE=F\$16,SUMMARY!\$R\$2:\$R\$1309/F\$18)))))))}

Please read the area of my suggested formula that deals with "Control". I gave two alternative ways of extending the scope of that criteria

a) use a range such as (control=F\$14:G\$14)
see the F14:G14 part

or
b) use {} example (control={"Profit","X"})

above a & b extracted from suggested formula

If you want to ignore the condition Control,
delete that part of the formula.
This message was edited by Dave Patton on 2002-09-19 11:28

Replies
11
Views
584
Replies
6
Views
313
Replies
8
Views
1K
Replies
8
Views
353
Replies
3
Views
1K

### Forum statistics

1,144,741
Messages
5,726,017
Members
422,653
Latest member
mntsiki ### 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