Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Match Fomulas?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Match Fomulas?
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 "Jennifer" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Match Fomulas?
Jennifer wrote...
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: .... 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. .... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting cells that match 2 criteria | Excel Discussion (Misc queries) | |||
Counting instances that 2 different columns match | Excel Worksheet Functions | |||
Query counting cells in a row that exactly match cells in another | Excel Discussion (Misc queries) | |||
Counting when both match | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions |