SUMIF with two conditions

[vinirato]

Hello, I am using the code below:

Sub Somase()
Dim dblAnswer As Double
Application.ScreenUpdating = False
Set ws1 = ActiveSheet
Set wb = Workbooks.Open("C:\Users\Vinicius\Desktop\teste.xlsm")
Set ws2 = wb.Sheets("Plan1")
ws1.Range("b1") = Application.WorksheetFunction.SumIf(ws2.Range("a1:a10"), _
ws1.Range("a1"), ws2.Range("b1:b10"))
wb.Close False
Application.ScreenUpdating = True
End Sub


But now i wanna to put a second condition. This second contion sums the quantity between certains dates.

For example. The first condition the code will look for the code and the second condition it will se if the date is between 04/07/10 - 10/07/10.

Someone can help me?

[Bob Phillips]

Sub Somase()
Const FORMULA As String = _
"=SUMPRODUCT(--('<sheet2>'!A1:A10='<sheet1>'!A1),--('<sheet2>'!C1:C10>=--""<startdate>"")," & _
"--('<sheet2>'!C1:C10<=--""<enddate>""),'<sheet2>'!B1:B10)"
Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim dblAnswer As Double
Dim evalFormula As String

Application.ScreenUpdating = False
Set ws1 = ActiveSheet
Set wb = Workbooks.Open("C:\Users\Vinicius\Desktop\teste.xlsm")
Set ws2 = wb.Sheets("Plan1")

evalFormula = Replace(Replace(Replace(Replace(FORMULA, _
"<enddate>", "2010-07-10"), _
"<startdate>", "2010-07-04"), _
"<sheet2>", ws2.Name), _
"<sheet1>", ws1.Name)

ws1.Range("D1") = Application.Evaluate(evalFormula)

wb.Close False
Application.ScreenUpdating = True

End Sub

[vinirato]

It didnt work for me. I changed the columns and the sheet name. Did I do something wrong?

Sub Somase()
Const FORMULA As String = _
"=SUMPRODUCT(--('<sheet2>'!B:B='<sheet1>'!X6),--('<sheet2>'!D:D>=--""<startdate>"")," & _
"--('<sheet2>'!D:D<=--""<enddate>""),'<sheet2>'!E:E)"
Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim dblAnswer As Double
Dim evalFormula As String

Application.ScreenUpdating = False
Set ws1 = ActiveSheet
Set wb = Workbooks.Open("C:\Users\CLIENTE\Desktop\Status.xlsx")
Set ws2 = wb.Sheets("Plan1")

evalFormula = Replace(Replace(Replace(Replace(FORMULA, _
"<enddate>", "2010-07-10"), _
"<startdate>", "2010-07-04"), _
"<sheet2>", ws2.Plan1), _
"<sheet1>", ws1.DPL - 320061)

ws1.Range("F17") = Application.Evaluate(evalFormula)

wb.Close False
Application.ScreenUpdating = True

End Sub

[vinirato]

Other thing. Can I change the end and the stardate for a cell?

For example:

"<enddate>", "P17"), _
"<startdate>", "C17"), _

Can I?

[Bob Phillips]

It didnt work for me. I changed the columns and the sheet name. Did I do something wrong?

Sub Somase()
Const FORMULA As String = _
"=SUMPRODUCT(--('<sheet2>'!B:B='<sheet1>'!X6),--('<sheet2>'!D:D>=--""<startdate>"")," & _
"--('<sheet2>'!D:D<=--""<enddate>""),'<sheet2>'!E:E)"
Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim dblAnswer As Double
Dim evalFormula As String

Application.ScreenUpdating = False
Set ws1 = ActiveSheet
Set wb = Workbooks.Open("C:\Users\CLIENTE\Desktop\Status.xlsx")
Set ws2 = wb.Sheets("Plan1")

evalFormula = Replace(Replace(Replace(Replace(FORMULA, _
"<enddate>", "2010-07-10"), _
"<startdate>", "2010-07-04"), _
"<sheet2>", ws2.Plan1), _
"<sheet1>", ws1.DPL - 320061)

ws1.Range("F17") = Application.Evaluate(evalFormula)

wb.Close False
Application.ScreenUpdating = True

End Sub

Didn't work in what way?

[Bob Phillips]

Other thing. Can I change the end and the stardate for a cell?

For example:

"<enddate>", "P17"), _
"<startdate>", "C17"), _

Can I?

You can.

Untested!



Sub Somase()
Const FORMULA As String = _
"=SUMPRODUCT(--('<sheet2>'!B:B='<sheet1>'!X6),--('<sheet2>'!D:D>=--Text('<sheet1>'!P17,""yyyy-mm-dd""))," & _
"--('<sheet2>'!D:D<=--Text('<sheet1>'!C17,""yyyy-mm-dd"")),'<sheet2>'!E:E)"
Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim dblAnswer As Double
Dim evalFormula As String

Application.ScreenUpdating = False
Set ws1 = ActiveSheet
Set wb = Workbooks.Open("C:\Users\CLIENTE\Desktop\Status.xlsx")
Set ws2 = wb.Sheets("Plan1")

evalFormula = Replace(Replace(FORMULA, "<sheet2>", ws2.Name), "<sheet1>", ws1.Name)

ws1.Range("F17").Value = Application.Evaluate(evalFormula)

wb.Close False
Application.ScreenUpdating = True

End Sub

[Paul_Hossler]

For example. The first condition the code will look for the code and the second condition it will se if the date is between 04/07/10 - 10/07/10


You could SumIf on the code, and then subtract the SumIf for dates NOT between 04/07/10 - 10/07/10

Not as elegant as some solutions maybe

More thinking about it, not too sure this would work -- sorry

Paul

[vinirato]

You can.

Untested!



Sub Somase()
Const FORMULA As String = _
"=SUMPRODUCT(--('<sheet2>'!B:B='<sheet1>'!X6),--('<sheet2>'!D:D>=--Text('<sheet1>'!P17,""yyyy-mm-dd""))," & _
"--('<sheet2>'!D:D<=--Text('<sheet1>'!C17,""yyyy-mm-dd"")),'<sheet2>'!E:E)"
Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim dblAnswer As Double
Dim evalFormula As String

Application.ScreenUpdating = False
Set ws1 = ActiveSheet
Set wb = Workbooks.Open("C:\Users\CLIENTE\Desktop\Status.xlsx")
Set ws2 = wb.Sheets("Plan1")

´Do I need to put the name of the sheet here???
evalFormula = Replace(Replace(FORMULA, "<sheet2>", ws2.Name), "<sheet1>", ws1.Name)

ws1.Range("F17").Value = Application.Evaluate(evalFormula)

wb.Close False
Application.ScreenUpdating = True

End Sub

[Bob Phillips]

No you don't, you just use what I gave you. You have already declared the sheets when you set the ws1 and ws2 variables.

[vinirato]

No you don't, you just use what I gave you. You have already declared the sheets when you set the ws1 and ws2 variables.

It returns me an #REF error

I am attching the worksheet to help understand.

[vinirato]

No you don't, you just use what I gave you. You have already declared the sheets when you set the ws1 and ws2 variables.

Thats the second worksheet where the code gets the information from.

[Bob Phillips]

Maybe this



Sub Somase()
Const FORMULA As String = _
"=SUMPRODUCT(--('[<book2>]<sheet2>'!$B:$B='<sheet1>'!$A<row>)," & _
"--('[<book2>]<sheet2>'!$D:$D>='<sheet1>'!<column1>$2)," & _
"--('[<book2>]<sheet2>'!$D:$D<'<sheet1>'!<column2>$2),'[<book2>]<sheet2>'!$E:$E)"
Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim dblAnswer As Double
Dim evalFormula As String

Application.ScreenUpdating = False
Set ws1 = ActiveSheet
Set wb = Workbooks("Status.xlsx") '.Open("C:\Users\CLIENTE\Desktop\Status.xlsx")
Set ws2 = wb.Sheets("Plan1")

With ws1.Range("F13")

evalFormula = Replace(Replace(Replace(Replace(Replace(Replace(FORMULA, _
"<row>", .Row), _
"<column1>", ColumnLetter(.Column)), _
"<column2>", ColumnLetter(.Column + 1)), _
"<book2>", wb.Name), _
"<sheet2>", ws2.Name), _
"<sheet1>", ws1.Name)

.Value = Application.Evaluate(evalFormula)
End With

'wb.Close False
Application.ScreenUpdating = True

End Sub

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
sColumn = Split(Columns(Col).Address(, False), ":")(1)
ColumnLetter = sColumn
End Function

[vinirato]

Maybe this



Sub Somase()
Const FORMULA As String = _
"=SUMPRODUCT(--('[<book2>]<sheet2>'!$B:$B='<sheet1>'!$A<row>)," & _
"--('[<book2>]<sheet2>'!$D:$D>='<sheet1>'!<column1>$2)," & _
"--('[<book2>]<sheet2>'!$D:$D<'<sheet1>'!<column2>$2),'[<book2>]<sheet2>'!$E:$E)"
Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim dblAnswer As Double
Dim evalFormula As String

Application.ScreenUpdating = False
Set ws1 = ActiveSheet
Set wb = Workbooks("Status.xlsx") '.Open("C:\Users\CLIENTE\Desktop\Status.xlsx")
Set ws2 = wb.Sheets("Plan1")

With ws1.Range("F13")

evalFormula = Replace(Replace(Replace(Replace(Replace(Replace(FORMULA, _
"<row>", .Row), _
"<column1>", ColumnLetter(.Column)), _
"<column2>", ColumnLetter(.Column + 1)), _
"<book2>", wb.Name), _
"<sheet2>", ws2.Name), _
"<sheet1>", ws1.Name)

.Value = Application.Evaluate(evalFormula)
End With

'wb.Close False
Application.ScreenUpdating = True

End Sub

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
sColumn = Split(Columns(Col).Address(, False), ":")(1)
ColumnLetter = sColumn
End Function


Thanks a lot for helpming me, but the code still not working.

I tryied the one you gave me nad it return me 0.

Then i tried to change like this

Sub Somase()
Const FORMULA As String = _
"=SUMPRODUCT(--('[<book2>]<sheet2>'!$B:$B='<sheet1>'!$X6<row>)," & _
"--('[<book2>]<sheet2>'!$D:$D>='<sheet1>'!<column1>!$P:$P)," & _
"--('[<book2>]<sheet2>'!$D:$D<'<sheet1>'!<column2>!$C:$C),'[<book2>]<sheet2>'!$E:$E)"
Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim dblAnswer As Double
Dim evalFormula As String

Application.ScreenUpdating = False
Set ws1 = ActiveSheet
Set wb = Workbooks("Status.xlsx") '.Open("C:\Users\CLIENTE\Desktop\Status.xlsx")
Set ws2 = wb.Sheets("Plan1")

With ws1.Range("F17")

evalFormula = Replace(Replace(Replace(Replace(Replace(Replace(FORMULA, _
"<row>", .Row), _
"<column1>", ColumnLetter(.Column)), _
"<column2>", ColumnLetter(.Column + 1)), _
"<book2>", wb.Name), _
"<sheet2>", ws2.Name), _
"<sheet1>", ws1.Name)

.Value = Application.Evaluate(evalFormula)
End With

'wb.Close False
Application.ScreenUpdating = True

End Sub

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
sColumn = Split(Columns(Col).Address(, False), ":")(1)
ColumnLetter = sColumn
End Function



And It returned #VALOR error.

What I need to do:
In Cell F17 I want the code to some Column E (of worksheet status) if the code is the same (Cell X6 of the "10 Planejamento AL") and if the date is between column C and P (of the "10 Planejamento AL")

[vinirato]

I am seding again the worksheet

[vinirato]

If someone could help me.

Thanks a lot

[vinirato]

Anyone??

[Bob Phillips]

I'll take another look tomorrow to see if I am any clearer than before.

[vinirato]

I'll take another look tomorrow to see if I am any clearer than before.

thanks a lot xld