?
Solved

How subtotal in excel based on if condition.

Posted on 2009-12-23
8
Medium Priority
?
592 Views
Last Modified: 2012-05-08
I have a report and wants to subtotal based on column B (Account description) with the following criteria.
If Account Description is "Account Payable-Control" or "Amort - Leasing Commissions"  then subtotal and name the row as Accounting
esle if
If Account Description is "Impermis Cost Transfer " or "Impermis Income Transfer"  then subtotal and name the row as Transfer Cost
else if
If Account Description is "Leasing Comm - Accum Amort " or "Leasing Commissions"  then subtotal and name the row as Leasing
Else
Account Description other than above then subtotal all other and name the row as othere
in the end of data Grand total.

In report I already have a button name Calculate Amont that calculate the subtotal based on each category of Account Description. Calcualte Amont have following code for subtotal.
See attachced report for more info

I was not able to attached the file as micro enable so to run the code copy the following code in excel as micro and run it.

Sub Button1_Click()
Dim rng As Range
Dim wks As Worksheet
Dim LastRow As Long
Dim LastRowMul As Long
Dim myRng As Range
Dim myRngMul As Range
Dim cell As Range
Set wks = ActiveSheet

Application.ScreenUpdating = False


With wks
.AutoFilterMode = False
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Columns(1).Insert
.Range("A9:a" & LastRow).Value = 1

'30 columns + one inserted
Set myRng = .Range("a2:a" & LastRow).Resize(, 31)

Application.DisplayAlerts = False
myRng.Subtotal groupby:=3, Function:=xlSum, totallist:=Array(4, 5), _
Replace:=True, pagebreaks:=False, _
summarybelowdata:=xlSummaryBelow
Application.DisplayAlerts = True

wks.UsedRange.ClearOutline

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("a2:a" & LastRow)

'.Columns(1).Value = .Columns(1).Value

myRng.RemoveSubtotal

myRng.Resize(, 2).AutoFilter field:=1, Criteria1:="1"
myRng.Resize(, 2).AutoFilter field:=2, Criteria1:="*total"

On Error Resume Next
myRng.Offset(1, 0).Resize(myRng.Rows.Count - 1, 1) _
.Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete
On Error GoTo 0

.AutoFilterMode = False
.Columns(1).Delete
End With

With Intersect(wks.Columns("A"), wks.UsedRange)
    .AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Set rng = .SpecialCells(xlCellTypeVisible)
    wks.ShowAllData
    rng.EntireRow.Hidden = True
    .SpecialCells(xlCellTypeVisible) = ""
    rng.EntireRow.Hidden = False
End With


'With Intersect(wks.Columns("B"), wks.UsedRange)
 '   .AdvancedFilter Action:=xlFilterInPlace, Unique:=True
 '   Set rng = .SpecialCells(xlCellTypeVisible)
 '   wks.ShowAllData
 '   rng.EntireRow.Hidden = True
 '   .SpecialCells(xlCellTypeVisible) = ""
 '   rng.EntireRow.Hidden = False
'End With


'wks.UsedRange.AutoFormat Format:=xlRangeAutoFormatClassic1, Font:=True, Border:=True, Pattern:=True
Application.ScreenUpdating = True
End Sub

 



 
Subtotal.xlsx
0
Comment
Question by:mas1963
  • 5
  • 2
8 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 26113760
mas1963,

Use a formula like this:

=SUMPRODUCT(($B$2:$B$39={"Accounts Payable - Control","Amort - Leasing Commissions"})*$C$2:$C$39)

It's in the attached file.

Patrick
Subtotals-01.xlsx
0
 

Author Comment

by:mas1963
ID: 26114116
Hi Partick,
Thanks for quick response.
I forgot to tell you that I am pulling the data from the SQL server baseed on Job id so Accounting, Transfer Cost, Leasing and Other sbutotal are dynamic i.e I could possible that other Job id may no have Account or leasing as Account description and how we can Grand total of  Accounting, Transfer Cost, Leasing and Other sbutotal .

Thanks again for your help.

0
 

Author Comment

by:mas1963
ID: 26114921
also length of Account Description  will be dynamic.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 45

Accepted Solution

by:
patrickab earned 750 total points
ID: 26115944
mas1963,

I have changed the formulae so that they include rows 2 to 10000 which should cover your needs - if not extend them in the formulae. They're in the attached file.

Patrick
Subtotals-02.xlsx
0
 
LVL 7

Expert Comment

by:aplusexpert
ID: 26130418
Hi,

Try the sumif formula

like "=SUMIF(A1:A7,"=3",B1:B7)"
0
 

Author Comment

by:mas1963
ID: 26139864
Hi
I have a Button named Subtotal using the following formula for subtotal,  
Application.DisplayAlerts = False
myRng.Subtotal groupby:=3, Function:=xlSum, totallist:=Array(4, 5), _
Replace:=True, pagebreaks:=False, _
summarybelowdata:=xlSummaryBelow
Application.DisplayAlerts = True

and after using the
Accounting=SUMPRODUCT((B2:B65535={"Accounts Payable - Control","Amort - Leasing Commissions"})*C2:C65535)
Transfer Cost=SUMPRODUCT((B2:B65535={"Impermis Cost Transfer","Impermis Income Transfer"})*C2:C65535)
Other==SUM(C2:C65535)-SUM(F2:F3)
Grand Total=SUM(F2:F4)


If I clicked the Sutotal button then the Grand Total also get added the subtotal value to Grand Total.
How I can I avoid to add the subtotal value to Grand Total.

Thanks for all help
0
 

Author Comment

by:mas1963
ID: 26141452
I used the following formula in Other
=SUMPRODUCT((RIGHT(B10:B65536,5)<>"Total")*1,D10:D65536)-SUM(I2:I5) and it work.
Is it a better way of doing above.

Thanks
Masood
0
 

Author Closing Comment

by:mas1963
ID: 31669456
with help of response I was able to achive the result
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question