Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

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.

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.