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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
 
Upvote 0
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)))))))}
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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
Back
Top