Get data from multiple criteria of single column

[sanjuss2]

Hi all,
pls find attachment of file

I want result specified in file

I used formula in K4 but showing error

{=SUM(SUMIFS($C$4:$F$15,$B$4:$B$15,"="&$J4,$A$4:$A$15,"="&$K$2,$C$2:$F$2,"="&$I$19:$I$20))}

[wk9128]

Cell J4 formula , Drag down

Please Login or Register  to view this content.

Cell K4 for,ula , Drag down

Please Login or Register  to view this content.

Cell L4 for,ula , Drag down

Please Login or Register  to view this content.

[sanjuss2]

Thanks for reply but it is hard code of column
for sample purpose I have selected alternate columns
Actually the product codes mentioned in column : I must be verified with Row 2 above header of table

If change product code for SEV 1 in column : I then total also get changed

[sanjuss2]

I have used following formula but it is not give result for selected columns of product, it is giving total of the branch for the date only for all products.

{=SUM(($C$4:$F$15)*(--($B$4:$B$15=$J4))*(--($A$4:$A$15=$K$2)),(($C$2:$F$2=$I$19:$I$20)))}

[Glenn Kennedy]

Maybe:

=LET(d,$A$4:$A$15,SUMPRODUCT((d=$K$2)*($B$4:$B$15=$J4)*INDEX($C$4:$F$15,SEQUENCE(COUNTA(d)),TRANSPOSE(MATCH($I$19:$I$20,$C$2:$F$2,0)))))

and

=LET(d,$A$4:$A$15,SUMPRODUCT((d=$K$2)*($B$4:$B$15=$J4)*INDEX($C$4:$F$15,SEQUENCE(COUNTA(d)),TRANSPOSE(MATCH($I$22:$I$23,$C$2:$F$2,0)))))

[wk9128]

I have used following formula but it is not give result for selected columns of product, it is giving total of the branch for the date only for all products.

{=SUM(($C$4:$F$15)*(--($B$4:$B$15=$J4))*(--($A$4:$A$15=$K$2)),(($C$2:$F$2=$I$19:$I$20)))}

Cell K4 formula , Drag down and across

Please Login or Register  to view this content.

[sanjuss2]

Hi,
Great !!!! Thanks a lot


Both formula works excellent !!!!!


I required additional condition to extract records on matching multiple rows viz. category

Please find attachment for your kind perusal...

[Glenn Kennedy]

It doesn't make any difference to the results... but...

=LET(d,$A$5:$A$16,SUMPRODUCT((d=$M$2)*($B$5:$B$16=$L4)*INDEX($C$5:$F$16,SEQUENCE(COUNTA(d)),TRANSPOSE(MATCH($K$19:$K$20&$L$19:$L$20,$C$3:$F$3&$C$2:$F$2,0)))))

see file.

[sanjuss2]

Thanks a lot

[wk9128]

@sanjuss2 You're Welcome. Glad to help . Thank You for the feedback and rep.