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