• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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 valueSum of qty
a??
d??


63213be 3.280"
 

Attachments

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
 
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?
 
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)
 
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.
 
Top