Count number of rows that meet two conditions

[travisg]

I hope my question is an easy one.

I need a formula that will count the number of rows that meet two conditions. In Column A I have a range called "Status_Ambrosio" and in Column B I have a range called "CSR_Ambrosio". The size of these ranges will change but they will always be identical sizes of each other. I need a formula that will count the number of rows where both conditions are met: Column A (Status_Ambrosio) equals "PROJECT" and Column B (CSR_Ambrosio) contains an asterisk (*). There will be other text in column B so I need the formula to check the cell in column B and see if it contains an asterisk. For Example, it would identify if a cell contained the name " James, Jesse* " but it would not identify a name typed " James, Jesse ". Can anyone help?

I'm lost as to whether this needs to be a count if, sumproduct, or sum function. Perhaps this needs to be an array? I'm just lost on this one, primarily on the Column B criteria. Any help would be appreciated. Thanks!

[benishiryo]

hi travisg, try:
=COUNTIFS(Status_Ambrosio,"project",CSR_Ambrosio,"*~**")

if it doesn't work, upload a sample Excel file. highlight those cells you expect to be counted

[travisg]

Thanks for the tip benishiryo. I've tried this before and it gives me #VALUE!. I'm not sure why. Here is a sampel of the table I am trying to use this formula on. Any one of the cells in Column A could potentially say PROJECT and any one of the names in Columns B/C could have an sterisk. Feel free to add/delete these for testing. Thanks for the help!

Excel_Forum.xlsx

[Marcol]

You have defined "CSR_Ambrosio" incorrectly it should be

Please Login or Register  to view this content.

Ahh .. the joys of merged cells.
Get rid of them.

[EDIT]
See the Names in this workbook, they are dynamic.
They will increase/decrease as you add/delete data from Column B