Hoping someone could please help me with the below, I am using the SUMIFS formula in my VBA code and it works for values except for when i am trying to find blanks.
Example source dataset:
| Date (column G in original sheet) | Customer (column O in original sheet) | TransactionValue (column E in original sheet) |
|---|---|---|
| 2021-02-26 05:45:00 GMT | 100 | |
| 2021-02-26 05:45:00 GMT | JohnDoe | 20 |
| 2021-02-26 07:12:18 GMT | JohnDoe | 15 |
| 2021-02-26 07:12:18 GMT | 75 | |
| 2021-02-26 12:22:55 GMT | JaneDoe | 28 |
| 2021-02-26 12:22:55 GMT | Joe Blogs | 85 |
I am then basically building something that looks a little like a pivot table, with the desired output being the total for each customer for each timestamp:
| Date | JohnDoe | JaneDoe | Joe Blogs | |
|---|---|---|---|---|
| 2021-02-26 05:45:00 GMT | 100 | 20 | ||
| 2021-02-26 07:12:18 GMT | 75 | 15 | ||
| 2021-02-26 12:22:55 GMT | 28 | 85 |
below is my code:
Dim r as Range
Dim finalcolumn as Long
finalcolumn = ActiveSheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Set r = Cells(2, 2)
With r
.Formula = "=SUMIFS(Results!$E:$E,Results!$O:$O,Static2!B$1,Results!$G:$G,Static2!$A2)"
.AutoFill Destination:=Range(Cells(2, 2), Cells(2, finalcolumn))
End With
My issue is that this doesn't return anything for the second column, i.e. where Customer is blank. It works fine for all other customer names.
Any ideas please?
CodePudding user response：
Try this:
.Formula = "=SUMIFS(Results!$E:$E,Results!$O:$O,IF(Static2!B$1="""","""",Static2!B$1),Results!$G:$G,Static2!$A2)"