Dynamic Cell Range within SUMPRODUCT/SUMIF formula

[regoroes]

Dear all,

I require a dynamic cell range within the SUMPRODUCT/SUMIF formula so that I can sum up the sales for a product if it is defined both individually and within a portfolio (Adidas) and Portfolio (Adidas/Nike)

within the calculation table.

=SUMPRODUCT(SUMIF(D:D,H4:H5,E:E))

As shown in the screenshot, H4:H5 comes from the Product-Portfolio table and the cell range needs to be dynamic based cell value in column A.

Can anyone advise on this?

Thanks!

 

[regoroes]

 

[Marcelo Branco]

The table Product-Portfolio in columns G:H is not necessary

Try
B3 copied down
=SUMPRODUCT(--ISNUMBER(SEARCH($D$3:$D$100,A3)),--(D$3:D$100<>""),$E$3:$E$100)

Hope this helps

M.

 

[regoroes]

Thank you Marcelo. This is an elegant solution, however I have a table defining the product to portfolio tagging. The portfolio name may not be based on a product name and "/" string combination in every case.

For example:

Portfolio: Sportswear
Sportswear: Adidas, Nike, Reebok

Is there another way that we can go about it?

The table Product-Portfolio in columns G:H is not necessary

Try
B3 copied down
=SUMPRODUCT(--ISNUMBER(SEARCH($D$3:$D$100,A3)),--(D$3:D$100<>""),$E$3:$E$100)

Hope this helps

M.

 

[regoroes]

Perhaps I have not been clear.

I have a table defining the product to portfolio tagging. The portfolio name may not be based on a product name and "/" string combination in every case.

Also, there is a long list of entry in the Product-Portfolio table and the entry may not be in order.

 

[Aladin Akyurek]

Would you post the data of these images directly here?

 

[regoroes]

Would you post the data of these images directly here?

[TABLE="class: grid, width: 612, align: center"]
<colgroup><col><col><col span="4"><col><col></colgroup><tbody>[TR]
[TD="align: left"]Calculation[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Sales[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Product-Portfolio[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Portfolio[/TD]
[TD="align: left"]Sales[/TD]
[TD][/TD]
[TD="align: left"]Prod[/TD]
[TD="align: left"]Sales[/TD]
[TD][/TD]
[TD="align: left"]Portfolio[/TD]
[TD="align: left"]Prod[/TD]
[/TR]
[TR]
[TD="align: left"]Adidas[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD="align: left"]Givenchy[/TD]
[TD="align: right"]2000[/TD]
[TD][/TD]
[TD="align: left"]Highstreet[/TD]
[TD="align: left"]H&M[/TD]
[/TR]
[TR]
[TD="align: left"]Sportswear[/TD]
[TD="align: right"]6000[/TD]
[TD][/TD]
[TD="align: left"]Adidas[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"][/TD]
[TD="align: left"]Sportswear[/TD]
[TD="align: left"]Adidas[/TD]
[/TR]
[TR]
[TD="align: left"]…[/TD]
[TD="align: left"]…[/TD]
[TD][/TD]
[TD="align: left"]Nike[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]
[TD="align: left"]Highstreet[/TD]
[TD="align: left"]Topshop[/TD]
[/TR]
[TR]
[TD="align: left"]…[/TD]
[TD="align: left"]…[/TD]
[TD][/TD]
[TD="align: left"]…[/TD]
[TD="align: left"]…[/TD]
[TD][/TD]
[TD="align: left"]Sportswear[/TD]
[TD="align: left"]Nike[/TD]
[/TR]
[TR]
[TD="align: left"]…[/TD]
[TD="align: left"]…[/TD]
[TD][/TD]
[TD="align: left"]…[/TD]
[TD="align: left"]…[/TD]
[TD][/TD]
[TD="align: left"]Sportswear[/TD]
[TD="align: left"]Converse[/TD]
[/TR]
[TR]
[TD="align: left"]…[/TD]
[TD="align: left"]…[/TD]
[TD][/TD]
[TD="align: left"]…[/TD]
[TD="align: left"]…[/TD]
[TD][/TD]
[TD="align: left"]Luxury[/TD]
[TD="align: left"]Givenchy[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Luxury[/TD]
[TD="align: left"]Chanel[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]…[/TD]
[TD="align: left"]…[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]…[/TD]
[TD="align: left"]…[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]…[/TD]
[TD="align: left"]…[/TD]
[/TR]
</tbody>[/TABLE]

Done

 

[Marcelo Branco]

Perhaps I have not been clear.

I have a table defining the product to portfolio tagging. The portfolio name may not be based on a product name and "/" string combination in every case.

Also, there is a long list of entry in the Product-Portfolio table and the entry may not be in order.

The data do not appear to be consistent - in column A are the Portfolios to be searched in column G to obtain the products in column H. However, mixed with such Portfolios in column A are also isolated products that do not appear in the column G.

Therefore, if we create a formula to get the components of the Sportwear portfolio (A4) - which would require something like IF(G3:G9=A4,H3:H9) - it would not work for Adidas because Adidas does not appear in column G.

Also strange is that in post 1 Reebok appears as Product in the Sales table and also as Portfolio in column G.

Finally, to create a robust formula, that works for isolated products and for portfolios, the data must be coherent.

M.

 

[regoroes]

Thank you for the reply. The idea is to sum up sales for Product OR Portfolio that is being defined in the Product-Portfolio table.

 

[Aladin Akyurek]

[TABLE="class: grid, width: 612, align: center"]
<tbody>[TR]
[TD="align: left"]Calculation[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Sales[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Product-Portfolio[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Portfolio[/TD]
[TD="align: left"]Sales[/TD]
[TD][/TD]
[TD="align: left"]Prod[/TD]
[TD="align: left"]Sales[/TD]
[TD][/TD]
[TD="align: left"]Portfolio[/TD]
[TD="align: left"]Prod[/TD]
[/TR]
[TR]
[TD="align: left"]Adidas[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD="align: left"]Givenchy[/TD]
[TD="align: right"]2000[/TD]
[TD][/TD]
[TD="align: left"]Highstreet[/TD]
[TD="align: left"]H&M[/TD]
[/TR]
[TR]
[TD="align: left"]Sportswear[/TD]
[TD="align: right"]6000[/TD]
[TD][/TD]
[TD="align: left"]Adidas[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"][/TD]
[TD="align: left"]Sportswear[/TD]
[TD="align: left"]Adidas[/TD]
[/TR]
[TR]
[TD="align: left"]…[/TD]
[TD="align: left"]…[/TD]
[TD][/TD]
[TD="align: left"]Nike[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]
[TD="align: left"]Highstreet[/TD]
[TD="align: left"]Topshop[/TD]
[/TR]
[TR]
[TD="align: left"]…[/TD]
[TD="align: left"]…[/TD]
[TD][/TD]
[TD="align: left"]…[/TD]
[TD="align: left"]…[/TD]
[TD][/TD]
[TD="align: left"]Sportswear[/TD]
[TD="align: left"]Nike[/TD]
[/TR]
[TR]
[TD="align: left"]…[/TD]
[TD="align: left"]…[/TD]
[TD][/TD]
[TD="align: left"]…[/TD]
[TD="align: left"]…[/TD]
[TD][/TD]
[TD="align: left"]Sportswear[/TD]
[TD="align: left"]Converse[/TD]
[/TR]
[TR]
[TD="align: left"]…[/TD]
[TD="align: left"]…[/TD]
[TD][/TD]
[TD="align: left"]…[/TD]
[TD="align: left"]…[/TD]
[TD][/TD]
[TD="align: left"]Luxury[/TD]
[TD="align: left"]Givenchy[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Luxury[/TD]
[TD="align: left"]Chanel[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]…[/TD]
[TD="align: left"]…[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]…[/TD]
[TD="align: left"]…[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]…[/TD]
[TD="align: left"]…[/TD]
[/TR]
</tbody>[/TABLE]

Done

In B3 control+shift+enter, not just enter, and copy down:

=SUM(SUMIFS($E$3:$E$5,$D$3:$D$5,IF(ISNUMBER(MATCH($A3,$D$3:$D$5,0)),$A3,IF($G$3:$G$9=$A3,$H$3:$H$9))))