#VALUE! Error for Single Criterion Sumproduct Function

[khameleon1]

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

[Aflatoon]

The issue is that you cannot perform array comparisons like that in VBA.

I would suggest you redo the calculations using Evaluate:

wc.Cells(2, 4) = ws.Evaluate("SumProduct(--(Month(AN2:AN" & lastRow & ")=2)")

for example

[khameleon1]

Thanks for replying! Unfortunately, when I copied in your example above, it's still gave me a Value Error. I also tried adding "Application." ahead of the Sumproduct function, but that produced the same result. Any thoughts on this would be much appreciated. I've been banging my head against the wall on this one for days.

[Aflatoon]

Sorry - missed a bracket out. It should be:

wc.Cells(2, 4) = ws.Evaluate("SumProduct(--(Month(AN2:AN" & lastRow & ")=2))")

[khameleon1]

Thank you! That worked beautifully. I need to do the same thing with January, and I read somewhere that January was different with Sumproduct, and that it would count blanks as January unless you told the code not to.

This is what I currently have for the "January" code:

wc.Cells(1, 4) = ws.Evaluate("SumProduct(--(Month(AN2:AN" & lastRow & ")=1))", --(Range("AN2:AN" & lastRow) <> ""))

is this right, or close anyway? When I try to run the code this way, it doesn't work. It gives me an error saying I'm applying the wrong number of arguments. Please help!

[Aflatoon]

Close-ish.

wc.Cells(1, 4) = ws.Evaluate("SumProduct((Month(AN2:AN" & lastRow & ")=1)*(AN2:AN" & lastRow & "<>""""))")

[khameleon1]

This appears to fix my issue. Thanks! You're AWESOME!!

I am now getting an automation error when trying to form the graph, but that appears to be unrelated to this thread or the sumproduct function.

Thanks again!

[Aflatoon]

Glad to help!