 # Column row-wise total based on Heading containing specific text

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

# Re: Column row-wise total based on Heading containing specific text

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.

NikolinoDE

# Re: Column row-wise total based on Heading containing specific text

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.

# Re: Column row-wise total based on Heading containing specific text

Here is some VBA code:

``````Sub InsertTotals()
Dim LastRow As Long
Dim StartCol As Long
Dim Col As Long
Dim OldCat As String
Dim NewCat As String
Dim FillColor As Long
Application.ScreenUpdating = False
Col = 3
StartCol = 3
Do
Col = Col + 1
If NewCat <> OldCat Then
Cells(HeaderRow, Col).Value = "Total " & OldCat
Range(Cells(HeaderRow + 1, Col), Cells(LastRow, Col)).FormulaR1C1 = _
"=SUM(RC[" & StartCol - Col & "]:RC[-1])"
.Borders.LineStyle = xlContinuous
.Interior.Color = FillColor - RGB(20, 20, 20)
End With
Col = Col + 1
StartCol = Col
OldCat = NewCat
End If
Loop Until NewCat = ""
Application.ScreenUpdating = True
End Sub``````

# Re: Column row-wise total based on Heading containing specific text

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)`` 