# Excel - Counting cells with given text only in rows and columns meeting criteria?

I am trying to write a formula to calculate the following:

How many rows contain "bruteforce" in column A and "Pass" in the column where row A contains "build three"?

Now, I have gotten somewhat close to this. This formula will give the total number of cells containing 'PASS' in the column in which row 1 contains "build three":

``````=COUNTIF(OFFSET(D2:Z3500, 0, MATCH("build three", D1:F1)-1, 3499,1), "Pass")
``````

However, I don't know where to go from here. An additional requirement is to avoid any VBA.

-

I would suggest using INDEX rather than OFFSET and if you use that within a COUNTIFS (with an "S") function you can include the column A criterion too, i.e.

=COUNTIFS(INDEX(D2:Z3500,0,MATCH("build three",D1:Z1,0)),"Pass",A2:A3500,"bruteforce")

-

Why not work in 2 step: create an extra column (f.e. in "G") and check if the conditions are met for that row. In some cell ("H2") count the number of "true" values in the range of "G".

Check this solution.

Edit:

For some reason, the google doc can't handle the match formula, so replace the formula in "G2" with this one:

``````=IF(A2="bruteforce";IF(INDEX(A\$1:G\$7;ROW(A2);MATCH("build three";A\$1:H\$1))="Pass";TRUE;FALSE);FALSE)
``````
-
Please note there are column conditions as well - I need to only count columns whose value in row 1 is "build three". This may be column F in my example, but it cannot be assumed to be column F. It could be any column. –  Adam S Apr 24 '12 at 14:45
Slipped my mind: edited now, should be deling with it now, or use barry's solutions, it seems good too. –  Terry Apr 24 '12 at 15:28