Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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"?

enter image description here

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.

share|improve this question
up vote 2 down vote accepted

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")

share|improve this answer

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.


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)
share|improve this answer
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .