Sum column based on cell value

goncalogera

New Member
Joined
Nov 10, 2020
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
Hi there,

I have a table called ("Vendas2020") that has the daily sales and each row has either "C" or "V" if they're either a purchase or a sale (C/V column is the fifth one and is called "Compra / Venda"). I have inserted a function to only calculate the visible values in the table since the destination worksheet (which is a report called "Relatório") has slicers for the products, salesperson and year. The code for the sumfunction is as follows:

VBA Code:
Function SUMVisible(Rg As Range)
Dim xCell As Range
Dim xCount As Integer
Dim xTtl As Double

Application.Volatile
Set Rg = Intersect(Rg.Parent.UsedRange, Rg)

For Each xCell In Rg
        If xCell.ColumnWidth > 0 _
          And xCell.RowHeight > 0 _
          And Not IsEmpty(xCell) _
          And IsNumeric(xCell.Value) Then
                xTtl = xTtl + xCell.Value
                xCount = xCount + 1
        End If
    Next
    If xCount > 0 Then
        SUMVisible = xTtl
    Else
        SUMVisible = 0
    End If
End Function

I'd like to add an if statement (or something like it) that only sums the values if they're purchases ("V") but I can't seem to make it work. It either throws a value error or a spill error. I can't seem to find a thread that has a solution that might apply in this case. I know it's something simple but I'm somewhat new to VBA.
Thanks in advance!
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,502
Office Version
  1. 365
Platform
  1. Windows
Firstly, do you need a vba function for this? Have I understood correctly that this can be done with a formula using standard worksheet functions?
I have done this in a single sheet but could be adapted to two sheets if it does what you want.

I don't know what all your table headers are but suppose you want to sum the visible rows in column F below, but only if "V" in the 'Compra / Venda' column.
The formula in K1 is doing that by adding all the green and yellow cells in column F ... but, I have no hidden rows at the moment.

goncalogera.xlsm
DEFGHIJK
1Col 1Col 2Col 3Col 4Compra / VendaCol 630
25973C5
37686V2
46922C2
58621C8
67546V1
75596C7
81954C8
99647C6
102817V3
119474V5
129165C1
139764V2
144838V8
154214C3
166818V1
177793C2
189317C2
191994C3
20
Sheet1
Cell Formulas
RangeFormula
K1K1=SUMPRODUCT(SUBTOTAL(109,OFFSET(Vendas2020[[#Headers],[Col 3]],ROW(Vendas2020[Col 3])-ROW(Vendas2020[[#Headers],[Col 3]]),,1)),--(Vendas2020[Compra / Venda]="V"))



Below, I have filtered column E to exclude the values 6 and 8 - and the K1 formula has adapted to remove the yellow cells above and now only sums the green ones.
Is that what you are trying to do?

goncalogera.xlsm
DEFGHIJK
1Col 1Col 2Col 3Col 4Compra / VendaCol 617
25973C5
46922C2
67546V1
75596C7
81954C8
119474V5
129165C1
139764V2
154214C3
177793C2
189317C2
191994C3
20
Sheet1
Cell Formulas
RangeFormula
K1K1=SUMPRODUCT(SUBTOTAL(109,OFFSET(Vendas2020[[#Headers],[Col 3]],ROW(Vendas2020[Col 3])-ROW(Vendas2020[[#Headers],[Col 3]]),,1)),--(Vendas2020[Compra / Venda]="V"))
 
Solution

goncalogera

New Member
Joined
Nov 10, 2020
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
Thank you very much for all your work, I think this works for my case!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,502
Office Version
  1. 365
Platform
  1. Windows
You are welcome. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,862
Messages
5,574,713
Members
412,613
Latest member
EFRATA
Top