Find Totals Based on Two Criterias in diff Cols and Rows

[prasaddn]

Hi All,

I am trying to find a formula and breaking my head for quite some time.

I will receive data as in the attached file. Unfortunately, I should not change the data format as it is used for further processing down the line.

i need total sub category based on product name.

the data will have huge list of products (product names), but sub category is constant.

For more clarity, see the attached file, when I enter the product name in the expected output cells, the totals of bottles and packets should come. I hope the requirement is clear.

Regards,
Prasad DN

 

[Hui]

H6: =SUMPRODUCT(($B$5:$B$20=$F$5)*($C$6:$C$21=G6)*($D$6:$D$21))
H7: =SUMPRODUCT(($B$5:$B$18=$F$5)*($C$7:$C$20=G7)*($D$7:$D$20))

 

[Khalid NGO]

H6: =SUMPRODUCT(($B$5:$B$20=$F$5)*($C$6:$C$21=G6)*($D$6:$D$21))
H7: =SUMPRODUCT(($B$5:$B$18=$F$5)*($C$7:$C$20=G7)*($D$7:$D$20))

Nice solution Sir,
I copied your formula and tried some experiments to learn more, I was trying to make formula drag-able.
I tried:

1)
=SUMPRODUCT((B$5:B19=F$5)*(C6:C20=G6)*(D6:D20))

2) with CSE:
=SUM(IF(B$5:B19=F$5,IF(C6:C20=G6,D6:D20)))

When I copy down both versions, 1st throws error #N/A
but 2nd shows correct result.

What would be the cause of different result ? as both formula's are structured with same range/criteria?

Thank you,

Khalid

 

[prasaddn]

Thank you Hui!! i knew about sumproduct, but did not realise the change in array of two parameters.

regards,
Prasad DN
PS: I should be good to go with this hint for rest of my work thank you.

 

[prasaddn]

Hi Again,

I am running into another issue here.. due to "" being multiplied by true(1) or false(0).

pls see the revised attached file. In the data range for sum, I have some cells which has formula like IFERROR(xxx,"").

Unfortunately, i cannot use IFERROR(xxx,0), as it would affect the downstream formulaes dependent on such cells.

Find attached revised file.

Kindly advise any workaround.

Regards,
Prasad DN
PS: Apologies for not bringing up this piece in my earlier question.

 

[prasaddn]

My bad luck: :S

With Khalid's CSE approach, I am able to overcome this problem:
with CSE:
=SUM(IF(B$5:B19=F$5,IF(C6:C20=G6,D6:D20)))

Regards,
Prasad DN