Feb 20 2023 11:37 PM
I have a sheet that contains several headings with amount below them in rows
I need sum of columns having heading containing SGST / CGST / IGST in separate columns as mention in result tables.
Feb 21 2023 12:33 AM
Here is a simple example of formula application in the attached file.
Using IF with AND, OR and NOT functions
The IF function allows you to make a logical comparison between a value and what you expect by testing for a condition and returning a result if that condition is True or False.
Hope I was able to help you with this info.
Feb 21 2023 11:16 PM
Perhaps I’ve not able to explain the matter properly.
I have several columns in a sheet
I managed to deleted the columns other than SGST, CGST & IGST thru VBA.
only columns remain are SGST, CGST & IGST having different tax categories (e.g. 5%, 18% etc)
now need to total of SGST row-wise, CGST row-wise & IGST row-wise
in separate column inserted automatically after the last column of each category.
Plz see the sheets attached.
Feb 22 2023 05:58 AM
Here is some VBA code:
Sub InsertTotals()
Const HeaderRow = 2 ' row number of the headers
Dim LastRow As Long
Dim StartCol As Long
Dim Col As Long
Dim OldCat As String
Dim NewCat As String
Dim FillColor As Long
LastRow = Cells(HeaderRow, 1).End(xlDown).Row
Application.ScreenUpdating = False
Col = 3
StartCol = 3
OldCat = Left(Cells(HeaderRow, Col).Value, 4)
FillColor = Cells(HeaderRow, Col).Interior.Color
Do
Col = Col + 1
NewCat = Left(Cells(HeaderRow, Col).Value, 4)
If NewCat <> OldCat Then
Range(Cells(HeaderRow, Col), Cells(LastRow, Col)).Insert Shift:=xlShiftToRight
Cells(HeaderRow, Col).Value = "Total " & OldCat
Range(Cells(HeaderRow + 1, Col), Cells(LastRow, Col)).FormulaR1C1 = _
"=SUM(RC[" & StartCol - Col & "]:RC[-1])"
With Range(Cells(HeaderRow, Col), Cells(LastRow, Col))
.Borders.LineStyle = xlContinuous
.Interior.Color = FillColor - RGB(20, 20, 20)
End With
Col = Col + 1
StartCol = Col
OldCat = NewCat
FillColor = Cells(HeaderRow, Col).Interior.Color
End If
Loop Until NewCat = ""
Application.ScreenUpdating = True
End Sub
Feb 22 2023 07:51 AM
A 365 solution to consider:
Dynamic range 'InputData' - this is all your data from column 3 to the end
=LET(n, Sheet1!$C$2:$M$10000, nonblank, COUNTA(TAKE(n, , 1)), TAKE(n, nonblank))
Dynamic range 'Names' - this is first two columns
=LET(n, Sheet1!$A$2:$B$10000, nonblank, COUNTA(TAKE(n, , 1)), TAKE(n, nonblank))
'Terms' - an array constant of terms for REDUCE to check
={"SGST"; "CGST"; "IGST"}
Lambda - 'Total' - this function filters each of the terms and stacks them horizontally. An additonal stack is created for the totals columns.
=LAMBDA(a,v,LET(
filtered, FILTER(InputData, TEXTBEFORE(TAKE(InputData, 1), " ") = v),
HSTACK(
a,
filtered,
BYROW(
filtered,
LAMBDA(row, IF(COUNT(row) = 0, TEXTBEFORE(TAKE(row, , 1), " ") & " Total", SUM(row)))
)
)
))
Sheet level formula:
=REDUCE(Names,Terms,Total)