Subtotal of a column of non blank cells of a different column

Akash Neel Hazarika

New Member
Dear Experts,

I am attaching a file where I need the sum of "Quantity" field if a lookup value matches with header name , I have named my columns like a,b,c,d----

Example for I need the sum of quantity for column 'B' , it should only consider the non blank cells and give me the sum of quanity column. The result should "
So my lookup value would be the name of the column ,
 Lookup value Sum of qty a ?? d ?? be 3.280"

Attachments

• 11.6 KB Views: 9

Chihiro

Excel Ninja
=AGGREGATE(9,7,INDEX(\$B\$3:\$I\$56,,MATCH(A62,\$B\$1:\$I\$1,0)))

Copy down.

If you change A62 to b. It will return 285054.18

Somendra Misra

Excel Ninja
Hi,

Another Option: Put in B62 below formula.

=SUBTOTAL(9,OFFSET(\$A\$1,,MATCH(A62,\$B\$1:\$I\$1,0),COUNTA(\$A\$2:\$A\$57)))
Regards.

Akash Neel Hazarika

New Member
Dear All, I have tried both the formulas but it is giving me the subtotal of c,d,e.... coulmns, but I need the subtotal of A coulmn ,that is quantity.

I have tried one formula ,which is working partially.

=SUMIF(\$B\$2:\$B\$57,"<>",\$A\$2:\$A\$57)
so in the above case \$B\$2:\$B\$57 range should be dynamic can we add 'match' in this case?

Akash Neel Hazarika

New Member
Hi,

Another Option: Put in B62 below formula.

Regards.
Hi I have tried your the formula but it is giving me the subtotal of c,d,e.... coulmns, but I need the subtotal of A coulmn ,that is quantity.

I have tried one formula ,which is working partially.

=SUMIF(\$B\$2:\$B\$57,"<>",\$A\$2:\$A\$57)
so in the above case \$B\$2:\$B\$57 range should be dynamic can we add 'match' in this case?

Chihiro

Excel Ninja
So you are not really after subtotal, but after SUMIF Column A where some other column isn't blank...?

=SUMIF(INDEX(\$B\$2:\$I\$57,,MATCH(A62,\$B\$1:\$I\$1,0)),"<>",\$A\$2:\$A\$57)

Akash Neel Hazarika

New Member
So you are not really after subtotal, but after SUMIF Column A where some other column isn't blank...?

=SUMIF(INDEX(\$B\$2:\$I\$57,,MATCH(A62,\$B\$1:\$I\$1,0)),"<>",\$A\$2:\$A\$57)
Thank a lot Chihiro! now it is working fine..

Akash Neel Hazarika

New Member
Thank a lot Chihiro! now it is working fine..
Hi Chihiro! may I pls ask your help in the above formula in an advanced scenerio? the above formula works fine if the ranges are fixed. If the ranges changes ,but coumn name & qty column remain same, wherever in the excel sheet, it should sum coumn 'QTY' matching other column is not blank.
I think VBA will work this for us.