Multiple functions use sum product with condition & Extracting range for each cell

[SKN2022]

Hello experts,
need help in extracting data from sheet
sample file attached
I used formula in I4, J5 cell but no value, so need to now what went wrong
now what I actually want is that
need to Extract data from column A named as Pile
for pile number 1 need sum product of B4:B20, H4:H20,
same for each pile number till 46

Second thing I want is for each pile range value from column H to be counted for different range as put in M3, N3, O3.

DATA 1.xlsx

Sheet1 Pile,Production,bench,Production,bench,SiO2,Al2O3,Fe2O3,CaO,LSF,Pile number,TOTAL SUMPRODUCT OF PRODUCTION * RANGE,RANGE BETWEEN 130 TO 150 ,RANGE above 150,RANGE BELOW 120 1,1164.48,I,11.38,I,11.38,1.38,0.50,46.61,138,1 1,1646.34,V,9.06,V,9.06,1.24,0.44,49.23,181,2 1,393.81,I,9.21,I,9.2...

docs.google.com

 

[jasonb75]

Do you want SUMPRODUCT, or SUM? B4*H4 or B4+H4?

=SUMPRODUCT(--(A4:A20=1),B4:B20,H4:H20)

=SUM(IF(A4:A20=1,B4:B20+H4:H20))

Note that the second one will not work if there are any non numeric entries in B4:B20 or H4:H20 (empty cells are ok, formula blanks are not).

It would be useful if you posted your example to the forum using XL2BB as a lot of members are limited by security restrictions that will not allow them to follow links to other sites.

 

[SKN2022]

i want sumproduct, B4*H4, and cant install XL2BB as laptop is company provided so cant install third party apps into it, the google drive file is shared in this forum multiple times , no one had issue for it.

 

[Joe4]

no one had issue for it.

Lots of people do, you just don't know it because they skip over your question and do not respond.
My workplace security policy strictly forbids us to download any files from the internet. And I know many others either cannot or will not download files from the internet.
There are others who can and will download your files. Just be aware that it reduces your pool of potential helpers.

So we usually advise people to upload files and provide links as a last resort, i.e. if they cannot use the other tools.

 

[jasonb75]

You didn't mention any problem with the suggested formula so I assume that it gives you what you need.

 

[SKN2022]

thanks for the reply, actually i got the solution from friend......................

 

[Joe4]

thanks for the reply, actually i got the solution from friend......................

Please post it here then, so the solution appears in the thread.
Then you can mark that post as the solution.

Thank you.

 

[SKN2022]

here is the solution sheet one had query with data, and sheet is having answer

DATA 1.xlsx

Sheet1 Pile,Production,bench,Production,bench,SiO2,Al2O3,Fe2O3,CaO,LSF,Pile number,TOTAL SUMPRODUCT OF PRODUCTION * RANGE,RANGE BETWEEN 130 TO 150 ,RANGE above 150,RANGE BELOW 120 1,1164.48,I,11.38,I,11.38,1.38,0.50,46.61,138,1 1,1646.34,V,9.06,V,9.06,1.24,0.44,49.23,181,2 1,393.81,I,9.21,I,9.2...

docs.google.com

 

[Joe4]

here is the solution sheet one had query with data, and sheet is having answer

DATA 1.xlsx

Sheet1 Pile,Production,bench,Production,bench,SiO2,Al2O3,Fe2O3,CaO,LSF,Pile number,TOTAL SUMPRODUCT OF PRODUCTION * RANGE,RANGE BETWEEN 130 TO 150 ,RANGE above 150,RANGE BELOW 120 1,1164.48,I,11.38,I,11.38,1.38,0.50,46.61,138,1 1,1646.34,V,9.06,V,9.06,1.24,0.44,49.23,181,2 1,393.81,I,9.21,I,9.2...

docs.google.com

Can you just post the formula/code that was the solution, instead of posting a link?

 

[SKN2022]

i dont think just posting the formula will make u understand the requirement/query, even than putting few of them

=(SUMIFS($F$18:$F$1066,$L$18:$L$1066,">=190",$D$18:$D$1066,D4))/D5%
=(SUMIFS($F$18:$F$1066,$L$18:$L$1066,">=160",$D$18:$D$1066,D4)-SUMIFS($F$18:$F$1066,$L$18:$L$1066,">=180",$D$18:$D$1066,D4))/D5%
=SUMPRODUCT(D6:AX6,$D$5:$AX$5)/$C$5
=(SUMIFS($F$18:$F$1066,$L$18:$L$1066,">=80",$D$18:$D$1066,D4)-SUMIFS($F$18:$F$1066,$L$18:$L$1066,">=100",$D$18:$D$1066,D4))/D5%