Sum column based on cell value

[goncalogera]

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:

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!

 

[Peter_SSs]

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
	D	E	F	G	H	I	J	K
1	Col 1	Col 2	Col 3	Col 4	Compra / Venda	Col 6		30
2	5	9	7	3	C	5
3	7	6	8	6	V	2
4	6	9	2	2	C	2
5	8	6	2	1	C	8
6	7	5	4	6	V	1
7	5	5	9	6	C	7
8	1	9	5	4	C	8
9	9	6	4	7	C	6
10	2	8	1	7	V	3
11	9	4	7	4	V	5
12	9	1	6	5	C	1
13	9	7	6	4	V	2
14	4	8	3	8	V	8
15	4	2	1	4	C	3
16	6	8	1	8	V	1
17	7	7	9	3	C	2
18	9	3	1	7	C	2
19	1	9	9	4	C	3
20
Sheet1

Cell Formulas
Range	Formula
K1	K1	=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
	D	E	F	G	H	I	J	K
1	Col 1	Col 2	Col 3	Col 4	Compra / Venda	Col 6		17
2	5	9	7	3	C	5
4	6	9	2	2	C	2
6	7	5	4	6	V	1
7	5	5	9	6	C	7
8	1	9	5	4	C	8
11	9	4	7	4	V	5
12	9	1	6	5	C	1
13	9	7	6	4	V	2
15	4	2	1	4	C	3
17	7	7	9	3	C	2
18	9	3	1	7	C	2
19	1	9	9	4	C	3
20
Sheet1

Cell Formulas
Range	Formula
K1	K1	=SUMPRODUCT(SUBTOTAL(109,OFFSET(Vendas2020[[#Headers],[Col 3]],ROW(Vendas2020[Col 3])-ROW(Vendas2020[[#Headers],[Col 3]]),,1)),--(Vendas2020[Compra / Venda]="V"))

 

[goncalogera]

Thank you very much for all your work, I think this works for my case!

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.