COUNTIF - more complex

[Guest]

I am needing a formula that will count text occurences in multiple columns,
similar to an a COUNTIF statment but using a filter quality. For example, I
need to know for all rows with the name of Joe in one column, how many times
Joe has a case "Open". I am using the 2000 version.

 

[T. Valko]

Try something like this:

=SUMPRODUCT(--(A1:A10="Joe"),--(B1:B10="Open"))

Better to use cells to hold the criteria:

D1 = Joe
E1 = Open

=SUMPRODUCT(--(A1:A10=D1),--(B1:B10=E1))

 

[Rick Rothstein \(MVP - VB\)]

I am needing a formula that will count text occurences in multiple columns,

similar to an a COUNTIF statment but using a filter quality. For example,
I
need to know for all rows with the name of Joe in one column, how many
times
Joe has a case "Open". I am using the 2000 version.

=SUMPRODUCT((A1:A100="Joe")*(C1:C100="Open"))

where I assumed column A had the name and column C had the status... change
then as appropriate. Also change the range to cove the maximum number of
rows you will need to handle. Probably better to put Joe and Open in their
own cells and test that instead of the string constant directly.

Rick