Column row-wise total based on Heading containing specific text

Brass Contributor

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.

4 Replies

@Vimal_Gaur 

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.

NikolinoDE

 

@NikolinoDE 

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.

@Vimal_Gaur 

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

@Vimal_Gaur 

 

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)

Patrick2788_0-1677081055337.png