COUNTIF with formula expression

Question

I have a grid with header names in the first row and first column, e.g.:

	A	B	C	D
A
B
C
D

The cells with the same header name in their row and column represent "valid" value for the given column.

For example, let's say I have this:

	A	B	C	D
A	1
B		3
C			1
D				4

In that case, "valid" value for the second column is "1", "valid" value for the third column is "3", "valid" value for the fourth column is "1", and "valid" value for the fifth column is "4".

Now for each row, I want to count how many valid values it has.

I'm using two conditional formattings that highlight the "valid" value and the values that are equal to it:

• YELLOW cell for "=B$1=$A2"
• GREEN cell for "=AND(NOT(ISBLANK(B2)),INDEX(B$2:B$10,MATCH(B$1,$A$2:$A$10,0))=B2)"

This is what it looks like:

screenshot

What I need is a formula that will count those values. Basically, a formula for that "COUNT" column at the end. On the screenshot, you can see what values it should calculate based on the current grid values.

Answer 1

Looking at it step by step, we can retrieve the valid values for each column with this:

=INDEX($B$2:$E$5,XMATCH($B$1:$E$1,$A$2:$A$5),XMATCH($B$1:$E$1,$B$1:$E$1))

The above formula is in cell H1 in the image.

We can check if a cell's value is equal to the valid value for that column:

=$B$2:$E$5=H1#

That formula is in cell H2 in the image.

Then we can count the number of TRUE values in each row:

=BYROW(H2#,LAMBDA(r,SUM(N(r*1))))

That formula is in cell N2 in the image. It applies the function SUM(N(row*1)) to each row in the check array. So wherever it finds TRUE, it has 1, and wherever it finds FALSE, it has zero. Summing those 1s and 0s gives the correct result.

If you want a single formula where you just select the whole table with headers included, you can use this (shown in cell Q2 in the image):

=LET(
    arr,$A$1:$E$5,
    colh,INDEX(arr,1,2):INDEX(arr,1,COLUMNS(arr)),
    rowh,INDEX(arr,2,1):INDEX(arr,ROWS(arr),1),
    dat,INDEX(arr,2,2):INDEX(arr,ROWS(arr),COLUMNS(arr)),
    valid,INDEX(dat,XMATCH(colh,rowh),XMATCH(colh,colh)),
    chk,dat=valid,
    BYROW(chk,LAMBDA(r,SUM(N(r*1))))
    )

• arr: the range of your table
• colh: the column headers of your table
• rowh: the row headers of your table
• dat: the data from your table without the headers
• valid: the valid values from the main diagonal (equivalent to first formula above)
• chk: the check whether a value in a row is equal to the valid value of that column (equivalent to the second formula above)
• then the last part is just the third formula above, giving the output

I would like to mention that a lot of the nonsense with INDEX:INDEX seen in colh, rowh and dat will be much simpler with the recently announced new functions TAKE, DROP, CHOOSEROWS and CHOOSECOLS, as described here. I don't have access to those functions currently, so I've used the old way.

Answer 2

An easy way to do it is to calculate the values on a separate set of columns.

You then assign the columns values to be zero (0) when there is no match or one (1) when you want to count them using your formulas.

Afterward, have another column that sums them up.

For a tidier presentation, you can hide these columns.

A more complex approach is to count the colored cells. Unfortunately, there isn't a native function to do so, but there are many approaches. However, they require advanced knowledge going into VBA functions and the like.

Here, I found one article that explains a few methods to count colored cells in Excel, or you can Google other options if that one doesn't fit.