Using SUMPRODUCT to SUM a Defined Range

Rewcifer

New Member
Joined
May 16, 2018
Messages
12
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!
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
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

New Member
Joined
May 16, 2018
Messages
12
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.
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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.
 
Last edited:

Rewcifer

New Member
Joined
May 16, 2018
Messages
12
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,970
Messages
5,471,765
Members
406,781
Latest member
aproberts1980

This Week's Hot Topics

Top