I am using a sumproduct formula to summarize data from an Excel table
(regular data area) subject to two variable criteria (Excel 2003).
the relevant data from the table is a column of items numbers (named
items), a column of divisions (named divisions), and a column of data
(named amounts). Active_item and Active-div are named single cells.
This formula works fine:

=SUMPRODUCT(--(items=active_item),--(divisions=active_div),amounts)

What I want to do, but can't get to work, is to build in a provision
where if the "active_div" is equal to "All", then the formula will
return the total "amount" subject only to a match of the
"active_item". When I build in an OR statement as part of my
active_div criteria, like

=SUMPRODUCT(--(items=active_item),--(OR
(divisions=active_div,active_div="All")),amounts)

the second term collapses to 1 and the sumproduct formula returns
#VALUE. Somehow I suppose I need to make sure I return an array of
1's and 0's in my second criteria term, and they should all be 1's if
active_div is equal to "All". If a specific scaler range named
"Active_div" is equal to "All" then I want to summarize the amounts,
subject only to the active_item condition.

Any help will be appreciated.

Thanks

Ken

Haven't tried but probably doable with an array =sum(if
But, why not make it simple

=if(active_div="all"),formulawithout that parameter, formulawithparameter)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

wrote in message
...
I am using a sumproduct formula to summarize data from an Excel table
(regular data area) subject to two variable criteria (Excel 2003).
the relevant data from the table is a column of items numbers (named
items), a column of divisions (named divisions), and a column of data
(named amounts). Active_item and Active-div are named single cells.
This formula works fine:

=SUMPRODUCT(--(items=active_item),--(divisions=active_div),amounts)

What I want to do, but can't get to work, is to build in a provision
where if the "active_div" is equal to "All", then the formula will
return the total "amount" subject only to a match of the
"active_item". When I build in an OR statement as part of my
active_div criteria, like

=SUMPRODUCT(--(items=active_item),--(OR
(divisions=active_div,active_div="All")),amounts)

the second term collapses to 1 and the sumproduct formula returns
#VALUE. Somehow I suppose I need to make sure I return an array of
1's and 0's in my second criteria term, and they should all be 1's if
active_div is equal to "All". If a specific scaler range named
"Active_div" is equal to "All" then I want to summarize the amounts,
subject only to the active_item condition.

Any help will be appreciated.

Thanks

Ken

Hi,

Try this

=sumproduct((items=active_item)+((divisions=active _div)*(active_div="All")),amounts)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

wrote in message
...
I am using a sumproduct formula to summarize data from an Excel table
(regular data area) subject to two variable criteria (Excel 2003).
the relevant data from the table is a column of items numbers (named
items), a column of divisions (named divisions), and a column of data
(named amounts). Active_item and Active-div are named single cells.
This formula works fine:

=SUMPRODUCT(--(items=active_item),--(divisions=active_div),amounts)

What I want to do, but can't get to work, is to build in a provision
where if the "active_div" is equal to "All", then the formula will
return the total "amount" subject only to a match of the
"active_item". When I build in an OR statement as part of my
active_div criteria, like

=SUMPRODUCT(--(items=active_item),--(OR
(divisions=active_div,active_div="All")),amounts)

the second term collapses to 1 and the sumproduct formula returns
#VALUE. Somehow I suppose I need to make sure I return an array of
1's and 0's in my second criteria term, and they should all be 1's if
active_div is equal to "All". If a specific scaler range named
"Active_div" is equal to "All" then I want to summarize the amounts,
subject only to the active_item condition.

Any help will be appreciated.

Thanks

Ken

Don

I thought about doing that. I actually have about 40 of these
formulas, and several have additional components, so I hoped to avoid
basically doubling the formulas with the =all and doesn't =all options
in an If statement. I suppose that is what I will do, unless I can
figure out Ashish' formula and make it work.

Thanks for this suggestion and all your other great stuff.

Ken

On Dec 10, 5:42*pm, "Don Guillett" wrote:
Haven't tried but probably doable with an array =sum(if
But, why not make it simple

=if(active_div="all"),formulawithout that parameter, formulawithparameter)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
wrote in message

...



I am using a sumproduct formula to summarize data from an Excel table
(regular data area) subject to two variable criteria (Excel 2003).
the relevant data from the table is a column of items numbers (named
items), a column of divisions (named divisions), and a column of data
(named amounts). *Active_item and Active-div are named single cells.
This formula works fine:

=SUMPRODUCT(--(items=active_item),--(divisions=active_div),amounts)

What I want to do, but can't get to work, is to build in a provision
where if the "active_div" is equal to "All", then the formula will
return the total "amount" subject only to a match of the
"active_item". *When I build in an OR statement as part of my
active_div criteria, like

=SUMPRODUCT(--(items=active_item),--(OR
(divisions=active_div,active_div="All")),amounts)

the second term collapses to 1 and the sumproduct formula returns
#VALUE. *Somehow I suppose I need to make sure I return an array of
1's and 0's in my second criteria term, and they should all be 1's if
active_div is equal to "All". *If a specific scaler range named
"Active_div" is equal to "All" then I want to summarize the amounts,
subject only to the active_item condition.

Any help will be appreciated.

Thanks

Ken- Hide quoted text -

- Show quoted text -

If your named ranges refer to entire columns this won't work unless you're
using Excel 2007.

Try this array formula** :

=SUM((items=active_item)*(IF(active_div="all",ROW( divisions)0,divisions=active_div))*amounts)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


wrote in message
...
Don

I thought about doing that. I actually have about 40 of these
formulas, and several have additional components, so I hoped to avoid
basically doubling the formulas with the =all and doesn't =all options
in an If statement. I suppose that is what I will do, unless I can
figure out Ashish' formula and make it work.

Thanks for this suggestion and all your other great stuff.

Ken

On Dec 10, 5:42 pm, "Don Guillett" wrote:
Haven't tried but probably doable with an array =sum(if
But, why not make it simple

=if(active_div="all"),formulawithout that parameter, formulawithparameter)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
wrote in message

...



I am using a sumproduct formula to summarize data from an Excel table
(regular data area) subject to two variable criteria (Excel 2003).
the relevant data from the table is a column of items numbers (named
items), a column of divisions (named divisions), and a column of data
(named amounts). Active_item and Active-div are named single cells.
This formula works fine:

=SUMPRODUCT(--(items=active_item),--(divisions=active_div),amounts)

What I want to do, but can't get to work, is to build in a provision
where if the "active_div" is equal to "All", then the formula will
return the total "amount" subject only to a match of the
"active_item". When I build in an OR statement as part of my
active_div criteria, like

=SUMPRODUCT(--(items=active_item),--(OR
(divisions=active_div,active_div="All")),amounts)

the second term collapses to 1 and the sumproduct formula returns
#VALUE. Somehow I suppose I need to make sure I return an array of
1's and 0's in my second criteria term, and they should all be 1's if
active_div is equal to "All". If a specific scaler range named
"Active_div" is equal to "All" then I want to summarize the amounts,
subject only to the active_item condition.

Any help will be appreciated.

Thanks

Ken- Hide quoted text -

- Show quoted text -

Biff
Thanks, that works great.
Ken

On Dec 11, 2:17*pm, "T. Valko" wrote:
If your named ranges refer to entire columns this won't work unless you're
using Excel 2007.

Try this array formula** :

=SUM((items=active_item)*(IF(active_div="all",ROW( divisions)0,divisions=ac*tive_div))*amounts)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

wrote in message

...
Don

I thought about doing that. *I actually *have about 40 of these
formulas, and several have additional components, so I hoped to avoid
basically doubling the formulas with the =all and doesn't =all options
in an If statement. *I suppose that is what I will do, unless I can
figure out Ashish' formula and make it work.

Thanks for this suggestion and all your other great stuff.

Ken

On Dec 10, 5:42 pm, "Don Guillett" wrote:



Haven't tried but probably doable with an array =sum(if
But, why not make it simple

=if(active_div="all"),formulawithout that parameter, formulawithparameter)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
wrote in message

....

I am using a sumproduct formula to summarize data from an Excel table
(regular data area) subject to two variable criteria (Excel 2003).
the relevant data from the table is a column of items numbers (named
items), a column of divisions (named divisions), and a column of data
(named amounts). Active_item and Active-div are named single cells.
This formula works fine:

=SUMPRODUCT(--(items=active_item),--(divisions=active_div),amounts)

What I want to do, but can't get to work, is to build in a provision
where if the "active_div" is equal to "All", then the formula will
return the total "amount" subject only to a match of the
"active_item". When I build in an OR statement as part of my
active_div criteria, like

=SUMPRODUCT(--(items=active_item),--(OR
(divisions=active_div,active_div="All")),amounts)

the second term collapses to 1 and the sumproduct formula returns
#VALUE. Somehow I suppose I need to make sure I return an array of
1's and 0's in my second criteria term, and they should all be 1's if
active_div is equal to "All". If a specific scaler range named
"Active_div" is equal to "All" then I want to summarize the amounts,
subject only to the active_item condition.

Any help will be appreciated.

Thanks

Ken- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


wrote in message
...
Biff
Thanks, that works great.
Ken

On Dec 11, 2:17 pm, "T. Valko" wrote:
If your named ranges refer to entire columns this won't work unless you're
using Excel 2007.

Try this array formula** :

=SUM((items=active_item)*(IF(active_div="all",ROW( divisions)0,divisions=ac*tive_div))*amounts)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

wrote in message

...
Don

I thought about doing that. I actually have about 40 of these
formulas, and several have additional components, so I hoped to avoid
basically doubling the formulas with the =all and doesn't =all options
in an If statement. I suppose that is what I will do, unless I can
figure out Ashish' formula and make it work.

Thanks for this suggestion and all your other great stuff.

Ken

On Dec 10, 5:42 pm, "Don Guillett" wrote:



Haven't tried but probably doable with an array =sum(if
But, why not make it simple

=if(active_div="all"),formulawithout that parameter,
formulawithparameter)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
wrote in message

...

I am using a sumproduct formula to summarize data from an Excel table
(regular data area) subject to two variable criteria (Excel 2003).
the relevant data from the table is a column of items numbers (named
items), a column of divisions (named divisions), and a column of data
(named amounts). Active_item and Active-div are named single cells.
This formula works fine:

=SUMPRODUCT(--(items=active_item),--(divisions=active_div),amounts)

What I want to do, but can't get to work, is to build in a provision
where if the "active_div" is equal to "All", then the formula will
return the total "amount" subject only to a match of the
"active_item". When I build in an OR statement as part of my
active_div criteria, like

=SUMPRODUCT(--(items=active_item),--(OR
(divisions=active_div,active_div="All")),amounts)

the second term collapses to 1 and the sumproduct formula returns
#VALUE. Somehow I suppose I need to make sure I return an array of
1's and 0's in my second criteria term, and they should all be 1's if
active_div is equal to "All". If a specific scaler range named
"Active_div" is equal to "All" then I want to summarize the amounts,
subject only to the active_item condition.

Any help will be appreciated.

Thanks

Ken- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -