Counting Match Fomulas?

[Guest]

I've got a few sheets in my workbook.

One sheet (titled Open Documents)I have imported from another program which
tells me document numbers, titles, originator or responsible person (this
column lists multiple people in one row), dates opened and closed, type of
document, etc. I'd like to be able to import this sheet on a regular basis
with up to date information, so I don't want to enter formulas here.

Another sheet where I am entering my formulas, I have peoples names listed
and a column with a function that counts each time a person's name appears in
the first sheet. I did that with this function:
=COUNTIF('Open Documents'!C:C,"*"&A1&"*")

On a third sheet (titled Open Important Documents) I have a list of
important documents that are not annotated as such on the first spreadsheet.

My question is: How do I count the number of important documents assigned
to each person? Basically, once it finds the documents assigned to one
person, I'd like it to check those rows for the document number and count
them if the document number is found on the third sheet as well.

I'm sure the Countif function I entered above will be part of the formula,
but I'm not sure if I use sumproduct, match, lookup or a combination of those
to get my results.

Thanks in advance,

Jennifer

 

[Guest]

I entered the following:
=COUNTIF('Open Documents'!A:A,AND('Open Documents'!C:C="*"&A1&"*",'Open
Documents'!A:A='Open Important Documents'!A:A))
This returned a zero, when I know there are a few.

I'm sure there could be something I'm missing in the second AND statement.
Maybe I'm not even on the right track.

Any suggestions?

Jennifer

 

[Harlan Grove]

I entered the following:
=COUNTIF('Open Documents'!A:A,AND('Open Documents'!C:C="*"&A1&"*",
'Open Documents'!A:A='Open Important Documents'!A:A))
This returned a zero, when I know there are a few.

....

COUNTIF supports a single criterion only.

"Jennifer" wrote: ....

....

If 'Open Documents'!A:A and 'Open Important Documents'!A:A both
contain document numbers (presumably distinct IDs), and if the names
in 'Open Documents'!C:C are separated by commas, you need to use
something like the array formula

=COUNT((SEARCH(" , "&A1&" , ",
TRIM(SUBSTITUTE(","&'Open Documents'!C$1:C$65535&",",","," , ")))
*MATCH('Open Documents'!A$1:A$65535,'Open Important Documents'!A:A,0))

Note that only the second argument to MATCH can be an entire column
range. The other arguments will be evaluated as arrays before the
functions are evaluated, so they can only span up to 65535 rows. Note
also that this is extremely inefficient in spreadsheets. If you have a
database you could use, it'd be much better for this.