Forum Discussion

Reg_B1944's avatar
Reg_B1944
Copper Contributor
Feb 01, 2024
Solved

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

 

  • Reg_B1944 

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

2 Replies

  • Reg_B1944 

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

    • Reg_B1944's avatar
      Reg_B1944
      Copper Contributor

      HansVogelaar thank you so much Hans, that works and I learned something new. Bonus.

       

      Take care

       

      Reg