About CountIF Function

Question

I'm new to Excel. I have an excel table and I want to make a formula to avoid manual process. Because sometimes it might be so sophisticated.

Here is the table I want to write formula. My main aim is to find to specific number which letter is assigned.

Letter	Number	Letter	Number
A	132	C	134
A	132	C	134
B	132	D	135
B	132	E	132
A	132	D	135
C	134	A	132
C	134

According to table, the expected result should be like below.

132	3	Because, it is used for A, B, and E letters
134	1	Because, it is used for only C letter
135	2

How to obtain a result like above?

Answer 1

I'll assume that for 135 it should be 1 and not 2 since D is repeated two times.

=LET(z,VSTACK(A2:B8,C2:D7),x,UNIQUE(TAKE(z,,-1)),HSTACK(x,MAP(x,LAMBDA(a,SUM(N(TAKE(UNIQUE(z),,-1)=a))))))

Result:

Answer 2

I will assume your data is in two ranges "Letters" and "Numbers" (if you actually have several columns, they can be combined using VSTACK() but we will skip this).

If each number is assigned to one and only one letter, and if the desired result is counting how many times each number appears -

Then what you need is very straightforward:

Left column (cell А1): =UNIQUE(Numbers)

Right column (cell B1): =COUNTIF(Numbers,А1#)

You really do not need Letters as all as long as every number is unique for every letter.

Let me know if you have a different algorithm in mind.

Answer 3

Assuming there is no Excel Constraints as per the tags posted, then one could use one of the following to arrive at the desired output. All the formulas posted below are one single input with dynamic array formula:

• Using GROUPBY() --> Applicable to MS365 Beta Version.

---

=LET(
     _Data, A2:D8,
     _Flip, TAKE(UNIQUE(WRAPROWS(TOCOL(_Data,1),2)),,-1),
     GROUPBY(_Flip,_Flip,COUNT,,0))

---

Or, Using MMULT()

=LET(
     _Data, A2:D8,
     _Flip, TAKE(UNIQUE(WRAPROWS(TOCOL(_Data,1),2)),,-1),
     UNIQUE(HSTACK(_Flip,MMULT(N(_Flip=TOROW(_Flip)),_Flip^0))))

---