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?

Calculation			Sales			Product-Portfolio
Portfolio	Sales		Prod	Sales		Portfolio	Prod
Adidas	1000		Givenchy	2000		Highstreet	H&M
Sportswear	6000		Adidas	1000		Sportswear	Adidas
…	…		Nike	5000		Highstreet	Topshop
…	…		…	…		Sportswear	Nike
…	…		…	…		Sportswear	Converse
…	…		…	…		Luxury	Givenchy
						Luxury	Chanel
						…	…
						…	…
						…	…

<colgroup><col><col><col span="4"><col><col></colgroup><tbody>
</tbody>

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]

Calculation			Sales			Product-Portfolio
Portfolio	Sales		Prod	Sales		Portfolio	Prod
Adidas	1000		Givenchy	2000		Highstreet	H&M
Sportswear	6000		Adidas	1000		Sportswear	Adidas
…	…		Nike	5000		Highstreet	Topshop
…	…		…	…		Sportswear	Nike
…	…		…	…		Sportswear	Converse
…	…		…	…		Luxury	Givenchy
						Luxury	Chanel
						…	…
						…	…
						…	…

<tbody>
</tbody>

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))))