Hello, I’m new to VBA and the forum, so please be kind!

I have been working on a project at work to reformat and do useful things with difficult to read data that outputs from our system. I’ve gotten pretty far on it, but I'm at a loss regarding my current step. What I am trying to do is create a graph to display approval timing by month, but can’t get past the “sumproduct” lines, shown below. I'm against using year with this formula, because I'd like this macro to be able to be used for future projects that span other years as well as this current one, attached.

I’ve seen other threads that address the exact same thing, but I get confused when trying to understand the help that is offered. The output I’m getting is a #VALUE! Error. I’ve searched the internet for explanations as to why I might be getting the error, but so far none make sense with the formula, since most reference different size ranges being called for the SUMPRODUCT formula. I can’t imagine this is the case here, since only one range is called out.

ws.Range("Z2:AQ" & lastRow).NumberFormat = "mm/dd/yyyy"
 
wc.Cells(1, 4) = [=Application.SumProduct(--('ws'.Range("AN2:AN" & lastRow) <> ""),--(Month('ws'.Range("AN2:AN" & lastRow)) = 1)]
wc.Cells(2, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 2))]
wc.Cells(3, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 3))]
wc.Cells(4, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 4))]
wc.Cells(5, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 5))]
wc.Cells(6, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 6))]
wc.Cells(7, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 7))]
wc.Cells(8, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 8))]
wc.Cells(9, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 9))]
wc.Cells(10, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 10))]
wc.Cells(11, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 11))]
wc.Cells(12, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 12))]
^^Each of these give #VALUE! Error

Charts.Add
With ActiveChart
    .ChartType = xlColumnClustered
    .SetSourceData Source:=wc.Range("D1:D12"), PlotBy:=xlColumns
^^As a result of the Errors above, I get “System Error &H800401A8 (-2147221080)”
    .Location Where:=xlLocationAsObject, Name:="Graphical Summary"
    .HasLegend = False
    .HasTitle = True
    .ChartTitle.Text = "PPAP Submission By Month"
    .SeriesCollection(1).XValues = wc.Range("C1:C12")
End With
'Make Top Left Corner start at H18
Dim CellsToCover3 As Range
Dim Cht3 As ChartObject
Set CellsToCover3 = wg.Range("H18:N35")
Set Cht3 = ActiveChart.Parent
Cht3.Height = CellsToCover3.Height
Cht3.Width = CellsToCover3.Width
Cht3.Top = CellsToCover3.Top
Cht3.Left = CellsToCover3.Left
Because of the size of my code, I’m only posting a small bit here. If needed, my entire code is in the attached Word Doc, and the starting file is in the excel doc.

Additional note: I'm sure there are "cleaner" ways of doing the same thing, and I'm 100% willing to take suggestions to clean up my code and make it more efficient, but I'm mostly concerned with function for now.

Please help!
PepsiCells&Analysis_Single Macro.docxPepsi.xlsx