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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

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,095,173
Messages
5,442,828
Members
405,199
Latest member
mkarnout

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top