Sumif using offset in vba

[djemy1975]

Dear developpers,

Could someone please help me to find out how to sum three cells from sheet into a userform using sumif and offsets

I have the following function:

'Sum of the amounts of paid and non paid invoices
Dim rB As Range
Dim dResult As Variant
Dim dOutput As Variant
Set rB = Range("B2", Range("B" & Rows.Count).End(xlUp))
Set rB = Worksheets("BDD").Range("G2:G1000")
dResult = Application.WorksheetFunction.SumIfs(rB.Offset(0, 4), rB, "Payée")
dOutput = Application.WorksheetFunction.SumIfs(rB.Offset(0, 4), rB, "Non Payée")
Label7 = dResult
Label8 = dOutput

The value is on column 7 (G) and the cell to be added is on colmn 4 (RB .Offset(0, 4) .till this step it is successful.What I want to add is the value on column 58 from rB ,i.e:rB.Offset(0, 58)

Is this possible?

THANKS IN ADVANCE,

[djemy1975]

Dear developpers,

Could someone please help me to find out how to sum three cells from sheet into a userform using sumif and offsets

I have the following function:
'Sum of the amounts of paid and non paid invoices
Dim rB As Range
Dim dResult As Variant
Dim dOutput As Variant
Set rB = Range("B2", Range("B" & Rows.Count).End(xlUp))
Set rB = Worksheets("BDD").Range("G2:G1000")

dResult = Application.WorksheetFunction.SumIfs(rB.Offset(0, 4), rB, "Payée")
dOutput = Application.WorksheetFunction.SumIfs(rB.Offset(0, 4), rB, "Non Payée")
Label7 = dResult
Label8 = dOutput

The value is on column 7 (G) and the cell to be added is on colmn 4 (RB .Offset(0, 4) .till this step it is successful.What I want to add is the value on column 58 from rB ,i.e:rB.Offset(0, 58)

Is this possible?

THANKS IN ADVANCE,

HEREWITH MY FILE for more clarifications

[skywriter]

Based on your sheet data, this is how I would define the columns where you show data.


Dim rngOne As Range
Dim rngTwo As Range
Dim rngThree As Range
With Worksheets("Sheet1").Range("G1").CurrentRegion
Set rngOne = .Offset(1).Resize(.Rows.Count - 1, 1) ' G column data.
Set rngTwo = rngOne.Offset(, 4) ' K column data.
Set rngThree = rngOne.Offset(, 9) ' P column data.
End With

[djemy1975]

your code is very nice and simplified ,but what i want is to sum column values for "payée" either from column k or column p and the same for "non payée"

using perhaps "if not isempty" for column k and p

[skywriter]

I don't know what you are saying.
SumIfs only accepts 1 range to sum as an argument.

DRESULT = Application.WorksheetFunction.SumIfs(rngTwo, rngOne, "Payée")
So just make another formula with rngThree,RngOne,"Payée" and add it to the first one and have that as the result.

As far as is not empty I don't know what you mean, it's a sum formula, if something is empty then it doesn't add to the total.

[djemy1975]

I have got this formula so far:

Sub etat()
Dim rngOne As Range
Dim rngTwo As Range
Dim rngThree As Range
Dim DRESULT As Variant
Dim DRESULT2 As Variant
Dim np As Variant
Dim np2 As Variant
With Worksheets("Sheet1").Range("G1").CurrentRegion
Set rngOne = .Offset(1).Resize(.Rows.Count - 1, 1) ' G column data.
Set rngTwo = rngOne.Offset(, 4) ' K column data.
Set rngThree = rngOne.Offset(, 9) ' P column data
DRESULT = Application.WorksheetFunction.SumIfs(rngTwo, rngOne, "Payée")
DRESULT2 = Application.WorksheetFunction.SumIfs(rngThree, rngOne, "Payée")
np = Application.WorksheetFunction.SumIfs(rngTwo, rngOne, "Non Payée")
np2 = Application.WorksheetFunction.SumIfs(rngThree, rngOne, "Non Payée")
paid = DRESULT + DRESULT2
nonpaid = np + np2
End With
End Sub

Private Sub UserForm_Initialize()
Call etat
End Sub

thank you very much as it ggives the right answer to me

[skywriter]

My pleasure, thanks for the feedback.
:beerchug: