Using SUMPRODUCT to SUM a Defined Range

[Rewcifer]

Hi all!

Was hoping someone might have some more insight into how I can leverage named ranges and SUMPRODUCT. My objective is to create a business score card that contains subcategories within overall brands. So, for example, think about Brand X containing items in Subcategory A, B, and C.

I'm trying to leverage the naming manager to help make this scorecard a little easier to update. I have a master data sheet that contains all of the brands and sub categories; additionally, I have defined names for each brand. Within these defined names are the relevant subcategories the brand sells items in. So, in other words, I want to just say something like:

SUMPRODUCT(--(Category_List=Brand_Name),Category_List_TY_Sales), and have the formula SUM all of the relevant subcategories that exist in the "Brand_Name" defined range.


I've tried a proof of concept for this, but keep receiving a #N/A error. Was wondering if anyone might be able to point out what I'm doing incorrectly here! Thank you for any and all help!

 

[theBardd]

Are the names similar size?

Can you post a sample of the data and tell us what result you want to make it clearer?

 

[Rewcifer]

Hi! They are not similar in size; however, I listed some formula examples below. In the dummy formulas, you'll see that I'm trying to get the same type of formula I listed first to work for the formula you see below. I'm attempting, then, to not have to hard code every brand manually; rather, I want to leverage the name manager to aggregate the subbrands that exist within a major brand "brands" tab and run the formula that way using sumproduct. However, I can't seem to get the given name to aggregate each department the brand is in in order to run the total formula. See below, and please let me know if you need any further information to get an idea of what I'm attempting to do!


Calculation I'm trying to simplify: =$C$10/SUMIF('FYTD Opstudies'!$B:$B,"Dept 3",'FYTD Opstudies'!$C:$C)

Leveraging name manager, here are the formulas I've attempted:

=SUMPRODUCT(--ISNUMBER(MATCH(Ops_List,Sub_3,0)),--(Ops_List_TY_Sales=Sub_3))
=SUMIF(Ops_List,Sub_3,Ops_List_TY_Sales)

Ops_list and Sub_3 are names I gave to aggregates in the name manager. For reference, Sub_3 refers to 3 different departments that need to be totaled in order for the original formulas to return the proper number.

 

[Marcelo Branco]

This does not seem correct to me
=SUMPRODUCT(--ISNUMBER(MATCH(Ops_List,Sub_3,0)),--(Ops_List_TY_Sales=Sub_3))

Try
=SUMPRODUCT(--ISNUMBER(MATCH(Ops_List,Sub_3,0)),Ops_List_TY_Sales)

M.

 

[tcardwell]

..or how about =sumproduct(sumifs(Ops_List_TY_Sales,Ops_List,Sub_3))

 

[Rewcifer]

This worked perfectly, Marcelo! Thank you so much.

I was wondering if you could help me understand the syntax a little better, as I'm drawing a blank on what I had done wrong originally. Any and all help would be appreciated!


Also thanks to everyone who replied! Absolutely love this forum.