Want to COUNTIF two different columns meet a criteria HELP!
Posted by Jeff on December 13, 2001 8:29 AM
I want to have a field calculate how many occurrences where column A = one criteria and column B = another criteria. For instance:
If the report lists a store location in one column and a inventory SKU type in another, I want to count how many times the location is "Joe" and the SKU is "Widget1" on the same row. This will tell me how many widgets Joe has.
Need help on this ASAP. Thanks so much!
Posted by Aladin Akyurek on December 13, 2001 8:33 AM
Jeff --
It's
=SUMPRODUCT((A2:A100="joe")*(B2:B100="Widget1"))
Aladin
========
Posted by Jeff on December 13, 2001 8:39 AM
It returns NUM#. Also, is there a way to put a wildcard in there like Widg* so that it returns any type of Widget?
Posted by Jeff on December 13, 2001 8:40 AM
It returns NUM#. Also, is there a way to put a wildcard in there like Widg* so that it returns any type of Widget?
Posted by Aladin Akyurek on December 13, 2001 8:45 AM
> It returns NUM#.
You probably tried using A:A and B:B as arguments. That is not allowed in sumproduct formulas. You must feed it with a real range. However, if you insist having A:A and B:B, let me know. There is a workaround it using an UDF.
> Also, is there a way to put a wildcard in there like Widg* so that it returns any type of Widget?
Yes. The formula then will be:
=SUMPRODUCT((A2:A100="Joe")*(ISNUMBER(SEARCH("Widget",B2:B100))))
Aladin
=========
Posted by Aladin Akyurek on December 13, 2001 8:47 AM
See below. [NT]
Posted by Jacob on December 13, 2001 8:54 AM
This might help
Sub Countit()
Application.ScreenUpdating = False
Dim key1
Dim key2
key1 = InputBox("What do you want to search for in the first column?")
key2 = InputBox("What do you want to search for in the second column?")
Y = 0
For x = 1 To 100
Range("A" & x).Select
If ActiveCell.Value = key1 Then
Range("B" & x).Select
If ActiveCell.Value = key2 Then
Y = Y + 1
End If
End If
Next x
MsgBox "There Are " & Y & " Matches To Your Query"
End Sub
Jacob
