Formula/vba - Summary Report without Pivot

[malleshg24]

Hi Team,
Need your help in Creating Summary Report with Excel Formula/VBA,instead of Pivot Table.
There will be 3 columns and 3 Criteria.
Fourth Column I have taken helper Column, for creating summary report via Pivot.
it will not be required if we want to prepare through formula/VBA


I have summary Template needs to fill up values from G2:J7. attached template for example.
I am ok for solution via vba or Excel formula. Thanks in advance for your help.





Thanks
mg

[paulked]

=COUNTIFS(A:A,"=A",B:B,">5",B:B,"<11",C:C,"<>Pen**",C:C,"<>Finished") in G2, change to suit the others.

[mana]

Option Explicit


Sub test()
Dim ProductList: ProductList = Array("A", "B", "C", "D", "E")
Dim NumArray: NumArray = Array(0, 6, 11, 16)
Dim r As Range
Dim v
Dim k As Long, y As Long, x As Long
Const Status As String = "Pen*"
ReDim w(1 To UBound(ProductList) + 1, 1 To UBound(NumArray) + 1) As Long

Set r = Range("G3").Resize(UBound(w, 1), UBound(w, 2))
v = Range("a1").CurrentRegion.Resize(, 3).Value

For k = 2 To UBound(v, 1)
If v(k, 3) Like Status Then
y = Application.Match(v(k, 1), ProductList, 0)
x = Application.Match(v(k, 2), NumArray, 1)
w(y, x) = w(y, x) + 1
End If
Next

r.Value = w

End Sub

[malleshg24]

Hi Paul and Mana,
Thanks a lot for your help, your suggested code is working.


Mana- I am using your code.
I modified one line as per my requirement its working.
If v(k, 3) Like "Pen*" or v(k, 3) Like "pen*" Then


How to add (OR) condition for below Const line if we can. like string = "Pen*" or "pen*"
Const Status As String = "Pen*"


Can you add comments for below line how to read it.
ReDim w(1 To UBound(ProductList) + 1, 1 To UBound(NumArray) + 1) As Long
w(y, x) = w(y, x) + 1
r.Value = w


Thanks
mg

[p45cal]

You can include all combinations of upper/lower case by defining Status all upper case:
Const Status As String = "PEN*"
then later your test will be:
If UCase(v(k, 3)) Like Status Then