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?
>Solution :
Try this:
.Formula = "=SUMIFS(Results!$E:$E,Results!$O:$O,IF(Static2!B$1="""","""",Static2!B$1),Results!$G:$G,Static2!$A2)"