Sumifs VBA #Value Error

X

xs4amit

New Member
Joined
May 21, 2018
Messages
34
Hi,

i am trying to do Sumifs using VBA, below is my Code:

Sub Mandays()
Application.ScreenUpdating = False


Dim Summ As Double
Dim WS1, WS2 As Worksheet
'Dim Arr1 As Variant
Dim Rng1, Rng2, Rng3, Rng4, Rng5 As Range
Dim tFilter1, tFilter2, tFilter3, tFilter5 As String
'Dim i As Long




Set WS1 = Sheets("Daily_Production_Stats")
Set WS2 = Sheets("Master_WDD")
'Arr1 = WS1.UsedRange.Value
Rng1 = WS2.Range("P2:P30000")
Rng2 = WS2.Range("F2:F30000")
Rng3 = WS2.Range("D2:D30000")
Rng4 = WS2.Range("J2:J30000")
tFilter1 = WS1.Range("C5")
tFilter2 = WS1.Range("D1")


With WS1
.Range("A5:A300").Clear


.Cells(5, 1).Value = .Application.WorksheetFunction.SumIfs(Rng1, Rng2, tFilter1, Rng4, tFilter2)


.Activate
End With


End Sub


I am getting #Value as a result whereas Manual Sumifs formula is returning accurate values. Am i missing something??
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
First problem is:

Code: 
Dim Rng1, Rng2, Rng3, Rng4, Rng5 As Range

You are not declaring Rng1 as range. Only Rng5 is range.

Therefore:

Code: 
Rng1 = WS2.Range("P2:P30000")

doesnt error. If declared as range it would error because you need to set ranges.

Code: 
Set Rng1 = WS2.Range("P2:P30000")
 
Upvote 0
steve the fish said:
First problem is:

Code: 
Dim Rng1, Rng2, Rng3, Rng4, Rng5 As Range

You are not declaring Rng1 as range. Only Rng5 is range.

Therefore:

Code: 
Rng1 = WS2.Range("P2:P30000")

doesnt error. If declared as range it would error because you need to set ranges.

Code: 
Set Rng1 = WS2.Range("P2:P30000")
Click to expand...


Hi Steve,

Your help is like a miracle. I can say enough thank you, you saved my day.
I modified the formula and created a loop as well:

Sub Mandays()
Application.ScreenUpdating = False


Dim Summ As Double
Dim WS1, WS2 As Worksheet
Dim Arr1 As Variant
Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, Rng4 As Range, Rng5 As Range
Dim tFilter1 As String, tFilter2 As String, tFilter3 As String, tFilter5 As String
Dim i As Long




Set WS1 = Sheets("Daily_Production_Stats")
Set WS2 = Sheets("Master_WDD")
Arr1 = WS1.UsedRange.Value
Set Rng1 = WS2.Range("P5:P30000")
Set Rng2 = WS2.Range("F5:F30000")
Set Rng3 = WS2.Range("D5:D30000")
Set Rng4 = WS2.Range("J5:J30000")
'tFilter1 = WS1.Range("C5")
tFilter2 = WS1.Range("D1")


With WS1
.Range("A5:A300").Clear
For i = 3 To UBound(Arr1)
tFilter1 = WS1.Cells(i, 3)
Summ = .Application.WorksheetFunction.SumIfs(Rng1, Rng2, tFilter1, Rng4, tFilter2)
.Cells(i, 1).Value = Summ / 480
Next i
.Activate
End With


End Sub




Now the only challenge is left with this is that sum range i.e Rng1 (first augument) exist 5 times with Rng4 ---- and Rng2 & Rng3 remains same. So i have to create five summs and add them at last.

Any other solution you have?
 
Upvote 0
Hi Steve
For example if i describe Rng1 as SumRange and Rng4 as next/dynamic criteria range.
So right now SumRange is in column "P" and criteria range is column "J", the next SumRange will be 7 column next to "P" i.e. "W" and criteria Rng4 will be 7 Column next to "J" i.e. "Q"
And the very next will be "AD" and "X" respectively.
And this is 5 times. So i need to do 5 sumifs and then have to sum all 5 sumifs at last and pass it to next "i"
 
Upvote 0
My modified code look like this, but not working. Can you please look into it. It is returning '0' right now.
But works well if i remove date criteria:

Code: 
Sub Mandays()
Application.ScreenUpdating = False


Dim Summ1 As Double, Summ2 As Double, Summ3 As Double, Summ4 As Double, Summ5 As Double
Dim WS1, WS2 As Worksheet
Dim Arr1 As Variant
Dim CrtRng1 As Range, CrtRng2 As Range, CrtRng3 As Range, CrtRng4 As Range, CrtRng5 As Range, CrtRng6 As Range, CrtRng7 As Range
Dim SumRng1 As Range, SumRng2 As Range, SumRng3 As Range, SumRng4 As Range, SumRng5 As Range
Dim tFilter1 As String, tFilter2 As String, tFilter3 As String, tFilter5 As String
Dim i As Long, Fstdate As Date, LstDate As Date, X As Long




Set WS1 = Sheets("Daily_Production_Stats")
Set WS2 = Sheets("Master_WDD")
Arr1 = WS1.UsedRange.Value
Set SumRng1 = WS2.Range("P2:P30000")
Set SumRng2 = WS2.Range("W2:W30000")
Set SumRng3 = WS2.Range("AD2:AD30000")
Set SumRng4 = WS2.Range("AK2:AK30000")
Set SumRng5 = WS2.Range("AR2:AR30000")
Set CrtRng1 = WS2.Range("F2:F30000")
Set CrtRng2 = WS2.Range("D2:D30000")
Set CrtRng3 = WS2.Range("J2:J30000")
Set CrtRng4 = WS2.Range("Q2:Q30000")
Set CrtRng5 = WS2.Range("X2:X30000")
Set CrtRng6 = WS2.Range("AE2:AE30000")
Set CrtRng7 = WS2.Range("AK2:AK30000")
tFilter2 = WS1.Range("D1")
Fstdate = WS1.Cells(2, 4)
X = WS1.Cells(2, Columns.Count).End(xlToLeft).Column
LstDate = WS1.Cells(2, X).Value
With WS1
    .Range("A3:A300").Clear
    
    For i = 3 To UBound(Arr1)
    tFilter1 = WS1.Cells(i, 3)
    Summ1 = .Application.WorksheetFunction.SumIfs(SumRng1, CrtRng1, tFilter1, CrtRng3, tFilter2, _
    CrtRng2, ">=" & Fstdate, CrtRng2, "<=" & LstDate)
    Summ2 = .Application.WorksheetFunction.SumIfs(SumRng2, CrtRng1, tFilter1, CrtRng4, tFilter2, _
    CrtRng2, ">=" & Fstdate, CrtRng2, "<=" & LstDate)
    Summ3 = .Application.WorksheetFunction.SumIfs(SumRng3, CrtRng1, tFilter1, CrtRng5, tFilter2, _
    CrtRng2, ">=" & Fstdate, CrtRng2, "<=" & LstDate)
    Summ4 = .Application.WorksheetFunction.SumIfs(SumRng4, CrtRng1, tFilter1, CrtRng6, tFilter2, _
    CrtRng2, ">=" & Fstdate, CrtRng2, "<=" & LstDate)
    Summ5 = .Application.WorksheetFunction.SumIfs(SumRng5, CrtRng1, tFilter1, CrtRng7, tFilter2, _
    CrtRng2, ">=" & Fstdate, CrtRng2, "<=" & LstDate)
    
    .Cells(i, 1).Value = (Summ1 + Summ2 + Summ3 + Summ4 + Summ5) / 480
Next i
Summ1 = 0
.Activate
End With
End Sub
 
Upvote 0
I dont really need your code. I need to understand what you are attempting to do. I cant really help until i understand that.
 
Upvote 0
steve the fish said:
I dont really need your code. I need to understand what you are attempting to do. I cant really help until i understand that.
Click to expand...

Hi Steve,

Source data is as below:

DateEmail IDAttendance StatusP_Task1Sub_Task1Minutes SpentProduction1P_Task2Sub_Task2Minutes SpentProduction2P_Task3Sub_Task3Minutes SpentProduction3
22/08/18MrAustin@bell.comPresentSurvey1Address_Verify233343Survey2Address_Verify9167Survey1Address_Edit3156
22/08/18Willium23@bell.comPresentSurvey3Address_Edit123343Survey3Address_Edit2958Survey1Address_Edit3246
22/08/18Marlo55@bell.comPresentSurvey1Address_Verify45346Survey4Address_Edit2456Survey1Address_Verify34346
22/08/18Mariana@bell.comPresentSurvey4Address_Edit75568Survey1Address_Verify49568Survey3Address_Edit65213
23/08/18MrAustin@bell.comPresentSurvey3Address_Edit4967Survey1Address_Edit52568Survey2Address_Edit56467
23/08/18Willium23@bell.comPresentSurvey3Address_Verify86252Survey2Address_Verify4245Survey3Address_Verify76579
23/08/18Marlo55@bell.comPlanned Leave
23/08/18Mariana@bell.comPresentSurvey4Address_Verify4245Survey1Address_Verify4356Survey4Address_Verify7658
24/08/18MrAustin@bell.comPresentSurvey1Address_Edit4036Survey2Address_Edit66467Survey1Address_Edit8756
24/08/18Willium23@bell.comPresentSurvey3Address_Edit643567Survey4Address_Verify656879Survey1Address_Verify32357
24/08/18Marlo55@bell.comPresentSurvey4Address_Edit54457Survey1Address_Edit78235Survey2Address_Verify313457
24/08/18Mariana@bell.comSurvey1Address_Verify28467Survey2Address_Edit09235Survey1Address_Edit7670

<tbody>
</tbody>


And Destination Data is like

P_Task >>>Survey1 (Drop Down List)
Total MinutesNames22/08/1823/08/1824/08/18
??MrAustin@bell.com(Production for the day)(Production for the day)(Production for the day)
??Willium23@bell.com
??Marlo55@bell.com
??Mariana@bell.com

<tbody>
</tbody>


I want to calculate the total number of Minutes Spent for the all the dates mentioned in Row number 2 (Dates will be consecutive, but may extend upto any number of columns ) when P_Task is Survey1 (Selected from Drop Down) for the email IDs mentioned in Column 2 in Destination data.
 
Last edited:
Upvote 0
Means I wants to do sumifs for minutes spent when task is selected from filter and date is between first date and last date in Row 2 for email Ids mentioned in column 2
 
Upvote 0
If you place your first table in A1:O13 and your second in A16:E21 can you then place in A18 this formula:

=SUMPRODUCT((LEFT($D$1:$O$1,6)="P_Task")*($B$2:$B$13=$B18)*($D$2:$O$13=$B$16)*(IF(ISNUMBER($F$2:$Q$13),$F$2:$Q$13,0)))

Enter it CTRL-SHIFT-ENTER.

Does it produce the answer you look for?
 
Upvote 0
You must log in or register to reply here.

Similar threads

JeffGrant
  • Solved
VBA Code used twice, keeps table formatting on one sheet, but not on the other
Replies
4
Views
233
smozgur
smozgur
E
  • Question
VBA Help on Data Copy
Replies
3
Views
612
Alex Blakenburg
A
2
  • Question
VBA Copy Data from Sheet to Email depending on Assignee
Replies
2
Views
348
2000km12
2
L
  • Question
VBA / Pivot Table / Comparing values from two fields and highlighting
Replies
2
Views
801
LEXCERM
L
E
  • Solved
Delete Entire Rows Based on Not Equal to Text
Replies
3
Views
526
_eNVy_
E

Forum statistics

Threads
1,214,653
Messages
6,120,749
Members
448,989
Latest member
mariah3

Share this page

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