Count with 3 preconditions and index/match

[KatK6]

I need a formula which will:

Count the 1s in a column found using an index/match combination IF they meet two preconditions.

Something like:
COUNTIF(INDEX A2:BEB6000,0,MATCH("Bund"&"*",A2:BEB6000,0))
IF A2:A6000 = 0
AND IF (INDEX A2:BEB6000,0,MATCH("Q3"&"*"&"Bund"&"*",A2:BEB6000,1))

I have also tried SUMPRODUCT, COUNTIFS and IF+IF+IF...C+S+E variations to no avail. I just can't seem to get all the commas and components in the right places.

 

[Aladin Akyurek]

Could you explain in words (not in "formulas") what you are after?

 

[KatK6]

Sure, I want it to count the instances of 1 in the column that contains the text ""Q12"&"*"&"Bund"&"*" within the range A2:BEB6000 if
where the same row in column with the text "Q3"&"*"&"Bund"&"*" in A2:BEB6000 has a value of 1
and where the row A2:A6000 has a value of 0.

I am counting 0s and 1s so a sum function may also work.

 

[Aladin Akyurek]

Sure, I want it to count the instances of 1 in the column that contains the text ""Q12"&"*"&"Bund"&"*" within the range A2:BEB6000 if
where the same row in column with the text "Q3"&"*"&"Bund"&"*" in A2:BEB6000 has a value of 1
and where the row A2:A6000 has a value of 0.

I am counting 0s and 1s so a sum function may also work.

Still unclear. Are Q3 and Q12 cell addresses or just text?
And it sounds like A2:BEB6000 must be equal to 1 and contain

""Q12"&"*"&"Bund"&"*"

and

"Q3"&"*"&"Bund"&"*"

at the same time. Surely something is missing here.

 

[KatK6]

Q3 and Q12 are text.

I need to the formula to assess the value of three different columns (two of which are found through matching text and taking the index that column) and count the number of cases that meet all three criteria. Basically I'm using the formula to filter in and out respondents (rows) based on their responses and limit the count to only respondents that meet all three criteria. The data range is A2:BEB6000

I want to count the rows in A2:BEB600 where Column A = 0 AND the column with the text "Q12" and "Bund" =1 AND the column with the text "Q3" and "Bund"=1.

The location of the columns with Q12 and Q3 changes with each new wave of data so I need to find them with index/match rather than using a column ref.

 

[Aladin Akyurek]

Q3 and Q12 are text.

I need to the formula to assess the value of three different columns (two of which are found through matching text and taking the index that column) and count the number of cases that meet all three criteria. Basically I'm using the formula to filter in and out respondents (rows) based on their responses and limit the count to only respondents that meet all three criteria. The data range is A2:BEB6000

I want to count the rows in A2:BEB600 where Column A = 0 AND the column with the text "Q12" and "Bund" =1 AND the column with the text "Q3" and "Bund"=1.

The location of the columns with Q12 and Q3 changes with each new wave of data so I need to find them with index/match rather than using a column ref.

Thus, there is a column of which the header contains the text bit Q12 and Bund and another that contains Q3 and Bund? And the header row is: A1:BEB1?

 

[KatK6]

Yes there is a header row that contains the text Q12 and Bund in one column and Q3 and Bund in another. The header row is: A2:BEB2.

 

[Aladin Akyurek]

Yes there is a header row that contains the text Q12 and Bund in one column and Q3 and Bund in another. The header row is: A2:BEB2.

One of...

=COUNTIFS(
  $A$3:$A$6000,0,
  INDEX($B$3:$BEB$6000,0,MATCH("*Q12*Bund*",$B$2:$BEB$2,0)),1,
  INDEX($B$3:$BEB$6000,0,MATCH("*Q3*Bund*",$B$2:$BEB$2,0)),1)

=SUMPRODUCT(
  --($A$3:$A$6000=0),
  --(INDEX($B$3:$BEB$6000,0,MATCH("*Q12*Bund*",$B$2:$BEB$2,0))=1),
  --(INDEX($B$3:$BEB$6000,0,MATCH("*Q3*Bund*",$B$2:$BEB$2,0))=1))

For the post-2003 systems, the formula with COUNTIFS is the most efficient option.