sumproduct problem

[krcowen]

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 Guillett]

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)

 

[Ashish Mathur]

Hi,

Try this

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[krcowen]

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

 

[T. Valko]

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


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

 

[krcowen]

Biff
Thanks, that works great.
Ken

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


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





- Show quoted text -

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Biff
Thanks, that works great.
Ken