Excel 2007 : Multiple Conditional Count Functions

Hello,

I require help to create formulas with the following requirements. I have also attached an example of the worksheet.

Tasks
1. Count the number of "Good" and "Required" in the column "PM2 Required" based upon the following matches:
Base location, Page, Section and Resp R&M column matches within a formula

2. Count the number of "Good", "NA" and "Required" in the column "Heat Test Required" based upon the following matches:
Base location, Page, Section and Resp R&M column matches within a formula

3. Count the number of entries in column "OTSD NOMEM" based upon the following matches:
Base location, Page, Section and Resp R&M column matches within a formula

Note: I know that part of the formula to correctly count the number of entries (there could be more than one entry per cell) would be –
SUM(IF(LEN(TRIM('Master'!j2:j199))=0,0,LEN(TRIM('Master'!j2:j199))-LEN(SUBSTITUTE('Master'!j2:j199," ",""))+1)) and to make the above array formula work, after typing in the formula, press ctrl+shift_enter

3. Count the number of entries in column "Other Open NTFY" based upon the following matches:
Base location, Page, Section and Resp R&M column matches within a formula

Note: I know that part of the formula to correctly count the number of entries (there could be more than one entry per cell) would be - SUM(IF(LEN(TRIM('Master'!j2:j199))=0,0,LEN(TRIM('Master'!j2:j199))-LEN(SUBSTITUTE('Master'!j2:j199," ",""))+1)) and to make the above array formula work, after typing in the formula, press ctrl+shift_enter

I thank you kindly in advance for your solutions!
JN Morrow

With the large number of combinations this produces, have you considered using SUBTOTAL() and filtering your list?

In J1 use the COUNTA option

Please Login or Register  to view this content.

e.g.
If you filter
"PM2 Required" for "Required"
"Base Location" for "Base1"
"Page" for "Aux_DH"
"Section" for "Bridle"
The function will return 10

Hope this helps

If Post #2 doesn't suit your needs, try this workbook.

The sheet "Lookups" has the unique values for various catagories.
Each catagory is a named range.
This is used for the drop-down validation lists in sheet "Data Returns" A2:D2
You could make these lists dynamic.

In this example in "Data Returns" E2

Please Login or Register  to view this content.

Gives the result based on your selection from the drop-downs, all four must be filled for the formula to return a result.

Can you work with this?