Forum Discussion
Reg_B1944
Feb 01, 2024Copper Contributor
Countif cell = "something" and related cell (same row) = "something else"
Hello,
Trying to calculate the gender balance in our organization based on column containing M or F but only for those who have paid their fees, indicated by an "X" in another column
So conceptually
Countif(gender,"M") only if Paid = "X"
then convert to text so I can concat(text(count of paid males), /, text(count of paid females))
tried
=CONCAT(TEXT(COUNTIF(IF(E9="X",Gender,"M",0)),"#"),"/",TEXT(COUNTIF(IF(E9="X",Gender,"M",0)),"#"))
but E9=X is a fixed value and I need the formula to refer to the value in column E on the same row
Help please, this is beyond me
Thanks
Reg
Use COUNTIFS:
=COUNTIFS(gender_range, "M", paid_range, "X")
So for example
=COUNTIFS(gender_range, "M", paid_range, "X")&"/"&COUNTIFS(gender_range, "M", paid_range, "M")