How to write a formula to count conditions in a range and then divide the results to get a percentag

[kdk850]

Not sure if possible, but how would you write a formula in one cell to count a row of cells that aren't filled in as black, then count the cells that have an "X" within that range and then divide the number of cells that have an "X" by the number of cells that aren't filled in as black?  I did a workaround and did each function separately in different cells, but wanted to see if it can be done in one function.

[#2]

Peço a gentileza de me corrigirem se estiver errado mas penso que...

Para contar o número de células em uma linha que contêm um “X” e dividir esse número pelo número de células que não estão preenchidas com a cor preta, você pode usar a seguinte fórmula: =COUNTIF(1:1, "X")/COUNTIF(1:1, NOT("black")). No entanto, observe que essa fórmula só funcionará se você tiver uma função VBA personalizada chamada NOT que verifica a cor de preenchimento de uma célula e retorna VERDADEIRO se a célula não estiver preenchida com a cor preta e FALSO caso contrário.

[#3]

Para personalizar a função NOT no VBA:  

1. Abra o editor do VBA no Excel pressionando Alt + F11.
2. No editor do VBA, clique em Inserir e selecione Módulo para criar um novo módulo.
3. No novo módulo, digite o seguinte código para criar uma função personalizada chamada NOT:

Function NOT(cell As Range, color As String) As Boolean
    If cell.Interior.Color = RGB(0, 0, 0) And color = "black" Then
        NOT = False
    Else
        NOT = True
    End If
End Function

Salve o módulo e feche o editor do VBA. (fonte de informação: Bing)

Observe que essa é apenas uma maneira de personalizar a função NOT no VBA do Excel. Você pode ajustar o código acima para atender às suas necessidades específicas.

@goncalvesgf 

[#4]

@kdk850 

Not sure if possible, but how would you write a formula in one cell to count a row of cells that aren't filled in as black, then count the cells that have an "X" within that range and then divide the number of cells that have an "X" by the number of cells that aren't filled in as black?  I did a workaround and did each function separately in different cells, but wanted to see if it can be done in one function.

If your workaround works, it would be possible to nest one of those formulas within the other, or, maybe even better, to nest both of them within a third along the lines of the following:

=(formula that counts cells with "X")/(formula that counts cells not filled in)

And if you have a Microsoft 365 subscription you could use the LET function, as in

=LET(black,(formula that counts black cells),xcells,(formula that counts "X"s),xcells/black)

[#5]

@mathetes 

Thanks, I tried both ways and got the nesting to work because I assigned the cells filled in with black with a "1" as I have other cells in the same row that are yellow which I want to count along with the x's

=COUNTIF(C71:AF71,"X")/COUNTIF(C71:AF71,"<>"&$BE$2)

for the let function, it worked but, it wasn't calculating correctly with the BE2 cell reference, so I had to put the "1" value

=LET(xcell,COUNTIF(C71:AF71,"x"),black,COUNTIF(C71:AF71,"<>1"),xcell/black)

If I just have a fill color in a cell and want to count by that color without having to put in a value is it possible to do that? 

[#6]

@kdk850 

If I just have a fill color in a cell and want to count by that color without having to put in a value is it possible to do that? 

If it is possible, it's not something I know how to do. (I've looked briefly and not found a method.)

But, on top of that, I'm not sure it would be a good practice even if it were possible. It would seem to me (not knowing your full situation, of course, so willing to be corrected on this)...it would seem to me that you'd be relying on a manual process to do something that could be automated (via Conditional Formatting), and would be more reliable. That is assuming that there is some consistent set of conditions that lie behind your wanting to fill cells with this or that color, and that those conditions are to be found in one or more pieces of data elsewhere in the spreadsheet.

So why don't you start with considering the reasoning behind the color fills that you might want to consider: are they reasons/conditions that could themselves be programmed/automated?