VBA SUMIF always returns 0, vlookup returns error

[yxz152830]

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!

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

 

[RoryA]

You know you're using the literal text "ABC" as the criterion, not the variable abc?

 

[yxz152830]

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]

You know you're using the literal text "ABC" as the criterion, not the variable abc?

I just tried this:

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]

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)

 

[yxz152830]

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.

date

day

Name

SS01

SS04

SS03

SS09

SS10

SS05

SS07

SCN

SS02

SS06

SS08

Grand Total

4/23/2021​

Fri

ABC

648​

1118​

306​

901​

546​

533​

924​

275​

190​

185​

5626​

4/23/2021​

Fri

CBA

10​

3​

6​

12​

4​

3​

6​

3​

2​

49​

4/23/2021​

Fri

DCG

320​

896​

848​

491​

217​

1231​

930​

164​

152​

1100​

2058​

8407​

4/23/2021​

Fri

DGG

785​

94​

705​

239​

501​

492​

247​

306​

292​

460​

1171​

5292​

4/23/2021​

Fri

BCA

1067​

247​

676​

6​

19​

975​

34​

636​

1013​

532​

5205​

4/23/2021​

Fri

DFG

332​

436​

512​

434​

392​

352​

607​

201​

166​

316​

1495​

5243​

4/23/2021​

Fri

BKJ

447​

204​

559​

663​

304​

685​

559​

265​

173​

414​

1016​

5289​

4/23/2021​

Fri

CTV

2780​

1164​

61​

974​

1150​

1237​

351​

933​

17​

8667​

4/23/2021​

Fri

DEL

773​

983​

134​

500​

1002​

209​

580​

301​

195​

177​

2​

4856​

4/23/2021​

Fri

SCN

5636​

2709​

4409​

19105​

7035​

2130​

496​

3137​

10754​

55411​

4/23/2021​

Fri

HAN

110​

198​

253​

602​

375​

653​

113​

78​

14​

403​

208​

3007​

4/23/2021​

Fri

SYY

12​

8​

6​

3​

9​

9​

15​

7​

3​

24​

5​

101​

4/23/2021​

Fri

KIG

477​

675​

1608​

11​

69​

2081​

82​

549​

512​

1150​

8​

7222​

4/23/2021​

Fri

CRB

75​

928​

488​

698​

707​

717​

685​

327​

37​

343​

1122​

6127​

4/23/2021​

Fri

CRP

58​

216​

18​

249​

63​

40​

99​

55​

51​

82​

70​

1001​

4/23/2021​

Fri

KIR

1​

38​

101​

73​

181​

25​

811​

118​

1348​

4/23/2021​

Fri

ANB

510​

217​

348​

13​

465​

2​

283​

358​

1​

2197​

4/23/2021​

Fri

Grand Total

11261​

11750​

12141​

23975​

12303​

11906​

6639​

4331​

3083​

9732​

17927​

125048​

 

[RoryA]

Your code is looking for ABC in the first column of the table.

 

[yxz152830]

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!