Counting Multiple Cells

[thehog6929]

I'm trying to return multiple criteria as a count.

e.g.

Store Item Price
Grocery Chicken $5
Grocery Ham $6
Hardware Hammer $4
Local Candy $1

I want the record where the Store= Grocery AND Item = Chicken

[Bob Phillips]

=SUMPRODUCT(--(A2:A20="Grocery"),--(B2:B20="Chicken")

Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range.

[thehog6929]

Ok, so instead of using stores and items I'll just use the real data. I want a cell to display a count when the Planner Name = Michelle and the File Type = Plan Amendment. Here's what I have so far, I'm getting a "Wrong Number of arguments or Invalid Property Assignment" error now!


Private Sub SumProduct()
'Dim Count As Integer
'MsgBox ActiveSheet.UsedRange.Rows.Count
Count = SumProduct(("PlannerName" = "Michelle"), ("PlanType" = "*Plan*"))

[Bob Phillips]

Private Sub SumProduct()

'Dim Count As Integer
'MsgBox ActiveSheet.UsedRange.Rows.Count
Count = Activesheet.Evaluate _
"SUMPRODUCT(--(PlannerName=""Michelle""),--(ISNUMBER(SEARCH(""Plan"",PlanType))))"


I am assuming PannerName and PlanType are named ranges.