Array Formulas

pgzr

New Member
Joined
Sep 16, 2002
Messages
9
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
Joined
Feb 15, 2002
Messages
4,976
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Sep 16, 2002
Messages
9
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)))))))}
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Feb 15, 2002
Messages
4,976
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Forum statistics

Threads
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.
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