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 -