# 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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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

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)))))))}

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.

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
4
Views
1K
Replies
0
Views
329
Replies
14
Views
375
Replies
0
Views
529
Replies
0
Views
1K

1,219,770
Messages
6,150,167
Members
450,937
Latest member
kattyg261

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