CODEWITHSUNDEEP
excel
Jorge Mendes
Jorge Mendes

Reputation: 97

Count values matching row and column variable

I need to count how many "1" that match a row and column condition.

Here's an example:

enter image description here

As I said, I want to count all "1" that cond1 matches "1" and cond2 matches "1". The result should be 2.

I've started with this function, without success:

=COUNTIF(INDEX(B4:C10,MATCH(1,A4:A10,0),MATCH(1,B3:C3,0)),1)

Thanks for your help!!

Upvotes: 0

Views: 789

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Use COUNTIFS and remove the match on column A into its own range and criteria:

=COUNTIFS($A$4:$A$10,1,INDEX($B$4:$C$10,0,MATCH(1,$B$3:$C$3,0)),1)

enter image description here

Another option using SUMPRODUCT():

=SUMPRODUCT((A4:A10=1)*(B3:C3=1)*(B4:C10=1))

enter image description here

Upvotes: 1

Related Questions