VBA SUMIF always returns 0, vlookup returns error

yxz152830

Active Member
Joined
Oct 6, 2021
Messages
358
Office Version
  1. 365
Platform
  1. Windows
Gurus,
I'm trying to sum across different tables based on day. Below is the code and always return zero. after multiple tries i switched to vlookup and it returns error.
I broke down the parts: gas is not empty, abc is not empty and matches the day after transformation, aaa =0 but ABC is in all the tables,kkk counts all the columns not a zero.
Also the variable bbb that i wanna calculate using vlookup returns error.
what is wrong with my below code? Thanks!

VBA Code:
For Each cell In Range("a1", "a300")
    If Left(cell.Value, 3) = "I/B" Then
        table_day = cell.Offset(-1, 0)
            For Each tbl In Sheets("ss").ListObjects            
                If WorksheetFunction.Text(Replace(Replace(tbl.Name, "s", ""), "_", "/"), "ddd") = table_day Then
                    For Each cell1 In Range(cell.Offset(1, 0), cell.Offset(27, 0))
                    gas = cell1.Value
                      abc = tbl.Name
                    aaa = WorksheetFunction.SumIf(tbl.ListColumns(1).DataBodyRange, "ABC", tbl.ListColumns(tbl.ListColumns.Count).DataBodyRange)
                    kkk = tbl.ListColumns.Count
                   
'                     bbb = WorksheetFunction.VLookup(cell.Value, tbl.DataBodyRange, tbl.ListColumns.Count)
                        cell1.Offset(0, 1).Value = cell1.Offset(0, 1).Value _
                            + WorksheetFunction.SumIf(tbl.ListColumns(1).DataBodyRange, cell1.Value, tbl.ListColumns(tbl.ListColumns.Count).DataBodyRange)
                       
                    Next cell1
                 End If
            Next tbl
     End If
Next cell
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,349
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You know you're using the literal text "ABC" as the criterion, not the variable abc?
 

yxz152830

Active Member
Joined
Oct 6, 2021
Messages
358
Office Version
  1. 365
Platform
  1. Windows
You know you're using the literal text "ABC" as the criterion, not the variable abc?
yea i was so confused about the result so I took "ABC" out to test if I put anything null in the criterion argument. ABC is in all tables but aaa is still 0
 

yxz152830

Active Member
Joined
Oct 6, 2021
Messages
358
Office Version
  1. 365
Platform
  1. Windows
You know you're using the literal text "ABC" as the criterion, not the variable abc?
I just tried this:
VBA Code:
Sub jadijaidajsdisa()
For Each cell In Range("a5", "a23")
a = WorksheetFunction.SumIf(Sheets("ss").ListObjects("ss2021_04_12").ListColumns(1).DataBodyRange, cell, Sheets("ss").ListObjects("ss2021_04_12").ListColumns(Sheets("ss").ListObjects("ss2021_04_12").ListColumns.Count).DataBodyRange)
Next cell
End Sub

still 0 but shouldnt be. I mustve written the SUMIF expression wrong but not sure where. First arg criteria range, 2nd criterion, 3rd sum range. It should be very straightforward.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,349
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Are you sure that:
1. The values in the last column of the table are really numbers? (if you select them all, do you see a sum value in the status bar?)
2. The values in the first column really match the data you are looking for (eg no leading or trailing spaces)
 
Solution

yxz152830

Active Member
Joined
Oct 6, 2021
Messages
358
Office Version
  1. 365
Platform
  1. Windows
Are you sure that:
1. The values in the last column of the table are really numbers? (if you select them all, do you see a sum value in the status bar?)
2. The values in the first column really match the data you are looking for (eg no leading or trailing spaces)
I tried to simply use sumif in cells and no problem occured. the data is good. Thing is I need to sum across tables.
I feel like things went wrong with how I refer to the ranges in sumif and vlookup otherwise the "bbb = WorksheetFunction.VLookup(cell.Value, tbl.DataBodyRange, tbl.ListColumns.Count)" shouldnt have gone wrong.
datedayNameSS01SS04SS03SS09SS10SS05SS07SCNSS02SS06SS08Grand Total
4/23/2021​
FriABC
648​
1118​
306​
901​
546​
533​
924​
275​
190​
185​
5626​
4/23/2021​
FriCBA
10​
3​
6​
12​
4​
3​
6​
3​
2​
49​
4/23/2021​
FriDCG
320​
896​
848​
491​
217​
1231​
930​
164​
152​
1100​
2058​
8407​
4/23/2021​
FriDGG
785​
94​
705​
239​
501​
492​
247​
306​
292​
460​
1171​
5292​
4/23/2021​
FriBCA
1067​
247​
676​
6​
19​
975​
34​
636​
1013​
532​
5205​
4/23/2021​
FriDFG
332​
436​
512​
434​
392​
352​
607​
201​
166​
316​
1495​
5243​
4/23/2021​
FriBKJ
447​
204​
559​
663​
304​
685​
559​
265​
173​
414​
1016​
5289​
4/23/2021​
FriCTV
2780​
1164​
61​
974​
1150​
1237​
351​
933​
17​
8667​
4/23/2021​
FriDEL
773​
983​
134​
500​
1002​
209​
580​
301​
195​
177​
2​
4856​
4/23/2021​
FriSCN
5636​
2709​
4409​
19105​
7035​
2130​
496​
3137​
10754​
55411​
4/23/2021​
FriHAN
110​
198​
253​
602​
375​
653​
113​
78​
14​
403​
208​
3007​
4/23/2021​
FriSYY
12​
8​
6​
3​
9​
9​
15​
7​
3​
24​
5​
101​
4/23/2021​
FriKIG
477​
675​
1608​
11​
69​
2081​
82​
549​
512​
1150​
8​
7222​
4/23/2021​
FriCRB
75​
928​
488​
698​
707​
717​
685​
327​
37​
343​
1122​
6127​
4/23/2021​
FriCRP
58​
216​
18​
249​
63​
40​
99​
55​
51​
82​
70​
1001​
4/23/2021​
FriKIR
1​
38​
101​
73​
181​
25​
811​
118​
1348​
4/23/2021​
FriANB
510​
217​
348​
13​
465​
2​
283​
358​
1​
2197​
4/23/2021​
FriGrand Total
11261​
11750​
12141​
23975​
12303​
11906​
6639​
4331​
3083​
9732​
17927​
125048​
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,349
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Your code is looking for ABC in the first column of the table.
 

yxz152830

Active Member
Joined
Oct 6, 2021
Messages
358
Office Version
  1. 365
Platform
  1. Windows
Are you sure that:
1. The values in the last column of the table are really numbers? (if you select them all, do you see a sum value in the status bar?)
2. The values in the first column really match the data you are looking for (eg no leading or trailing spaces)
omg yea my vision impaired again. Thanks!
 

Forum statistics

Threads
1,176,276
Messages
5,902,295
Members
434,959
Latest member
etb1025

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
Top