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!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Are the names similar size?

Can you post a sample of the data and tell us what result you want to make it clearer?
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top