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:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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"))
 
Upvote 0
Solution
Thank you very much for all your work, I think this works for my case!
 
Upvote 0
You are welcome. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top