sum-up only filtered data (contains different suffixes)

[relio]

hello, this is relio new here, i have problem and uploaded xl-file for reference
simple daily account data contains figures with suffix needed to sumup the values by the reference of items on left, i tried subtotal with sumproduct and messed up.

thanx in advance for any help.

relio

 

[David Evans]

I've modified your worksheet to make your data range an Excel Table - the filtering will result from the Table's features.

There are some issues with your addition of numbers given that you're mixing grams and Kgs - you can have Excel add kgs as a label after your numbers (suffixes as you refer to them), but it makes sense to define the measure for the numbers and stick to that. You have a number of measures in the qty - units, feet, grams, liters, bundles and cbs ..... if you need to sum different measures, you'll need to add a column to your table to define a measure. Summing different measures will produce no useful answers; counting them may have some insight. On the latter note, your example also appears to be counting 5 items when there are only four.

 

[relio]

Hi, thanx for response (David), adding another column for suffix is a straight solution, but here boys carelessly put any suffix, so as per bill (some time may have / may not have suffix), i need the solution (summing up values with suffix) which can trim all suffix inside the formula and then sum-up the values (avoiding table feature) becoz sheet has date issue with login time, am trying other ways, if i get the sol. i will share here for further any improvement.

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[rahulshewale1]

hii @relio

See if is ok ?
Please find the attached solution using power query and Pivot table with Slicer ,


Regard
Rahul shewale

 

[relio]

hi, rahul & to all i saw power query and Pivot table with Slicer is a good and neat way of data representation, but here it needs like "put the formula & get the result", personally i would like to learn more on power query and Pivot table with Slicer. thanx for this. i have uploaded all three responses for others.
if any solution i get, i will share here, thanx.

 

[relio]

hello, to all, need help on this topic...

 

[NARAYANK991]

Hi ,

Can you explain exactly what problems you face with the 3 solutions which have been offered ?

Narayan

 

[relio]

hello,

1) sol. its not working for other values (its good to avoid table)
2) sol. i don't want values get separated with suffix, when items are filtered then sum-up of qty will be shown in the top row
3) sol. working fine, i tried this, data on 1-sheet and result on another but here (others both ways) did not like.

 

[bosco_yip]

hello,

1) sol. its not working for other values (its good to avoid table)
2) sol. i don't want values get separated with suffix, when items are filtered then sum-up of qty will be shown in the top row
3) sol. working fine, i tried this, data on 1-sheet and result on another but here (others both ways) did not like.

Hi,

Sutotal sum-up without values get separated with suffix



1] A4 helper, copied down :

=IF(B4="","",SUBTOTAL(103,B4))

and, select A4:A23 >> Custom Format Cells, in the type box enter: ;;;

2] Count formula, B2 enter :

=SUBTOTAL(103,B4:B23)

3] Total sum formula, C2 enter :

=SUMPRODUCT((A4:A23=1)*TEXT(MID(TEXT(MID(C4:C23&"a",1,COLUMN(B:Q)),),1,COLUMN(A: P)),"0.000;;;\0"))

Regards
Bosco

 

[relio]

hi, bosco,
your sol. saved me, friend, thanx for working on this problem,

just for curiosity asking, am really sorry, can we solve it without adding a new column becoz as narayan's sol. (separation of suffix & summing values) was a straight way solution with adding a column to data.

friends am just typing a thought no disregards for other wonderful solutions, i have uploaded all solutions may be useful for others. waiting for reply.
thanx,
with regards,
relio

 

[Thangavel]

hi, bosco,
your sol. saved me, friend, thanx for working on this problem,

just for curiosity asking, am really sorry, can we solve it without adding a new column becoz as narayan's sol. (separation of suffix & summing values) was a straight way solution with adding a column to data.

friends am just typing a thought no disregards for other wonderful solutions, i have uploaded all solutions may be useful for others. waiting for reply.
thanx,
with regards,
relio

Hello relio,
Thanks for consolidating different ways to subtotal data.

 

[bosco_yip]

hi, bosco,...........
just for curiosity asking, am really sorry, can we solve it without adding a new column ...........relio

The helper additional column can be removed,

1] Total sum formula, C2 enter :

=SUMPRODUCT(TEXT(MID(TEXT(MID(C4:C23&"a",1,COLUMN(B:U)),),1,COLUMN(A:T)),"0.000;;;\0")*SUBTOTAL(103,INDIRECT("C"&ROW(4:23))))

2] See attached revise file

Regards
Bosco

 

[relio]

hi, bosco... thanx and sorry for late to respond, i had some issues here but
ur's 2nd solution worked for me and it resulted correctly till now. if any issues raised i will inform you, again i have uploaded all solution for others.
thankyou
with regards
relio

 

[bosco_yip]

hi, bosco... thanx and sorry for late to respond, i had some issues here but
ur's 2nd solution worked for me and it resulted correctly till now. if any issues raised i will inform you, again i have uploaded all solution for others.
thankyou
with regards
relio

Perhaps……..

In "Bosco's Solu._2" sheet "Sl no." A4, formula copied down :

=SUBTOTAL(103,B$4:B4)

Regards
Bosco

 

[relio]

hi Thangavel D,
i have uploaded all soln. last solution of bosco's worked for me...

with regards
relio