Need help to sum colomn f based on value in colomn b

[dotchieJack]

Hello,
I Have this

  TextBox24.Value = Application.Sum(Sheets(sSheetName).Columns(8))

But I need something like

 If colomn B value = x then Application.Sum(Sheets(sSheetName).Columns(8))

Hope somebody can help me out

 

[Asheesh]

Can you share the sample workbook with the required output..it would be easier to understand...

 

[dotchieJack]

Hello,
For some reason i can not upload the file
Sorry for that

 

[dotchieJack]

Hello,
can it be because i am a new member?

 

[Deepak]

dotchieJack

Use sumifs

 

[dotchieJack]

Can you give me an example, I tryed with sumproduct, sumifs but i do not find the solution
Thanks

 

[Asheesh]

Why don't you try on the below lines..

Sub test()

Dim Result As Long
Dim LastRow As Long
Dim SumRange As Range
Dim CriteriaRange As Range
Dim Criteria As String

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Criteria = "x"

Set SumRange = Sheets("Sheet1").Range("A1:A" & LastRow)
Set CriteriaRange = Sheets("Sheet1").Range("B1:B" & LastRow)

Result = WorksheetFunction.SumIfs(SumRange, CriteriaRange, Criteria)

MsgBox Result


End Sub

See the attached for reference..

 

[Deepak]

 

[dotchieJack]

Hoi,
@Deepak, thank you for your answer but i want it in VBA in a userform
@Asheesh, thank you for your answer, I have to look into it, but i have 4 criteria
As example
for the first critera that goes into textbox 1 (on a userform) vba should look into colomn B for value "abc" (value in textbox should be the sum of colomn f)
For the second critera that goes into tetbox 2 vba should look in colomn B for value "fgh" (value in textbox should be the sum of colomn f)
For the 3 criteria that goes in textbox 3 vba should look in colomn c for the value "6" (value in textbox should be the sum of colomn f)
For the 4 criteria that goes in textbox 4 vba shouls look in colom c for the value "21" (value in textbox should be the sum of colomn f)

 

[Asheesh]

There are several ways of doing this however, you can do this using the above approach as well…try doing it yourself, if you get stuck show up here…we are happy to help…

 

[dotchieJack]

Hello,
@Asheesh, your example does exact what i want, now i have to find out to how to apply it to a userform
thanks

 

[Asheesh]

You can assign the result variable or the worksheetfunction to the textbox..

something like

textbox1.value = Result

Or

textbox1.value =  WorksheetFunction.SumIfs(SumRange, CriteriaRange, Criteria)

 

[dotchieJack]

Hoi,
@Asheesh

Private Sub CommandButton3_Click()
Dim sSheetName As String
Dim Result As Long
Dim LastRow As Long
Dim SumRange As range
Dim CriteriaRange As range
Dim Criteria As String

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Criteria = "x"
Set SumRange = ActiveSheet.range("H17:H" & LastRow)
Set CriteriaRange = ActiveSheet.range("B17:B" & LastRow)

 sSheetName = ActiveSheet.Name
 TextBox12.Value = Application.Sum(Sheets(sSheetName).Columns(5))
 TextBox22.Value = Application.Sum(Sheets(sSheetName).Columns(7))
 TextBox24.Value = Application.Sum(Sheets(sSheetName).Columns(8))
 TextBox26.Value = Result

End Sub
[CODE] 
I have this now but the result of textbox 26 = 0
Can you help out?

 

[Asheesh]

Come back after a while, about to leave for home...will get back as soon as I reach home..

 

[dotchieJack]

Great,
Thank you

 

[Asheesh]

Hi,

Hope this helps

Sub test()
Dim sSheet As Worksheet
Dim Result_1 As Long, Result_2 As Long, Result_3 As Long, Result_4 As Long, LastRow As Long

Set sSheet = ThisWorkbook.ActiveSheet 'Give the sheet name as required

LastRow = Cells(Rows.Count, "F").End(xlUp).Row


    Result_1 = WorksheetFunction.SumIfs(sSheet.Range("F2:F" & LastRow), sSheet.Range("B2:B" & LastRow), "abc")
    Result_2 = WorksheetFunction.SumIfs(sSheet.Range("F2:F" & LastRow), sSheet.Range("B2:B" & LastRow), "fgh")
    Result_3 = WorksheetFunction.SumIfs(sSheet.Range("F2:F" & LastRow), sSheet.Range("C2:C" & LastRow), "6")
    Result_4 = WorksheetFunction.SumIfs(sSheet.Range("F2:F" & LastRow), sSheet.Range("C2:C" & LastRow), "21")

' Once you have the outputs, you can assign the value stored in Result variables to textboxes..
'something like

TextBox1.Value = Result_1
TextBox2.Value = Result_2
TextBox3.Value = Result_3
TextBox4.Value = Result_4

End Sub

 

[dotchieJack]

Hoi,
Thank you for your answer and your great help
When I test your example it works perfect, unfortunatly when I try to put it into my code I get an error 91 (something with blockvariable and with (hard to translate for me)
I gues i have to puzzle

You have been a great help, thank you

 

[dotchieJack]

Hello Asheesh,
I did find it and it works like a charm, with your code
Thanks again for the great help
Problem solved

 

[Asheesh]

Glad it helped...