CODEWITHSUNDEEP
excel
syreeni
syreeni

Reputation: 35

Number of rows with specific text

How to get the number of rows which includes A, AA, AAA, AB, AAC etc.? On the following example result is 2. If the same row has A on different shapes on many cells it's only calculated as one.

Cell A1: AA, B1: A, A2: AAABABC, A3: BC

The following only calculates if rows has one A but not rows with AA or AAA

=SUM(--(MMULT(--(A1:C10="A"); TRANSPOSE(COLUMN(A1:C10)))>0))

The following calculates how many A's one row has but not the amount of rows.

=SUMPRODUCT((LEN($A1:$C10)-LEN(SUBSTITUTE($A1:$C10;"A";"")))/LEN(1))

Upvotes: 1

Views: 52

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34370

If you just want to count how many rows contain at least one A, it should just be a slight alteration to your MMULT formula:

=SUM(--(MMULT(--(ISNUMBER(FIND("A",A1:C10))), TRANSPOSE(COLUMN(A1:C10)))>0))

entered as an Array formula using CtrlShiftEnter.

Upvotes: 1

Related Questions