CODEWITHSUNDEEP
excelexcel-formula
Faraday
Faraday

Reputation: 1

Excel formula to Count within clusters of rows (defined by same value) and values for the adjacent column is the same for the given cluster

I have a spreadsheet containing a large dataset and would need a formula to achieve the following (see image attached):

Stackoverflow Excel Request

The objective is to have Excel perform the following:

1- Identify and cluster the rows, based on identical values in col. A (in this case, we should have clusters "Alpha, Bravo, Charlie")

2- Inside every cluster, return a new count (called, for example, "Individual count") if all the values in col. B for the cluster are "Individual"

3- If any row contains value "Entity" in col. B for a given cluster, return a new count (called, for example, "Entity count").

Based on the above example, the output should be as:

Individual Count = 1 (only Alpha contains only "Individual" associated to its rows) Entity Count = 2 (both Bravo and Charlie contain, at least, "Entity" associated to its rows)

Upvotes: 0

Views: 504

Answers (1)

Roger H.
Roger H.

Reputation: 336

The following formula is indeed very lengthy and took quite a while to arrive at.There were so many variables to account for.I tried the Frequency function, but could not get it to work with the text cells, including variations of other functions.The formula can be placed in cell C1 and filled down as far as needed:

=IF(IF(AND(A1<>A2,A1="Bravo"),SUM(IF(A$1:A$10&B$1:B$10="BravoIndividual",1,""),0))/SUM(IF(A$1:A$10&B$1:B$10="BravoIndividual",1,""),0)+IF(AND(A1<>A2,A1="Bravo"),SUM(IF(A$1:A$10&B$1:B$10="BravoEntity",1,""),0))/SUM(IF(A$1:A$10&B$1:B$10="BravoEntity",1,""),0)+IF(AND(A1<>A2,A1="Alpha"),SUM(IF(A$1:A$10&B$1:B$10="AlphaIndividual",1,""),0))/SUM(IF(A$1:A$10&B$1:B$10="AlphaIndividual",1,""),0)+IFERROR(IF(AND(A1<>A2,A1="Alpha"),SUM(IF(A$1:A$10&B$1:B$10="AlphaEntity",1,""),0)),"")*IFERROR(SUM(IF(A$1:A$10&B$1:B$10="AlphaEntity",1,"")),"")+IF(AND(A1<>A2,A1="Charlie"),SUM(IF(A$1:A$10&B$1:B$10="CharlieIndividual",1,""),0))/SUM(IF(A$1:A$10&B$1:B$10="CharlieIndividual",1,""),0)+IFERROR(IF(AND(A1<>A2,A1="Charlie"),SUM(IF(A$1:A$10&B$1:B$10="CharlieEntity",1,""),0)),"")*IFERROR(SUM(IF(A$1:A$10&B$1:B$10="CharlieEntity",1,"")),"")<1,"",IF(AND(A1<>A2,A1="Bravo"),SUM(IF(A$1:A$10&B$1:B$10="BravoIndividual",1,""),0))/SUM(IF(A$1:A$10&B$1:B$10="BravoIndividual",1,""),0)+IF(AND(A1<>A2,A1="Bravo"),SUM(IF(A$1:A$10&B$1:B$10="BravoEntity",1,""),0))/SUM(IF(A$1:A$10&B$1:B$10="BravoEntity",1,""),0)+IF(AND(A1<>A2,A1="Alpha"),SUM(IF(A$1:A$10&B$1:B$10="AlphaIndividual",1,""),0))/SUM(IF(A$1:A$10&B$1:B$10="AlphaIndividual",1,""),0)+IFERROR(IF(AND(A1<>A2,A1="Alpha"),SUM(IF(A$1:A$10&B$1:B$10="AlphaEntity",1,""),0)),"")*IFERROR(SUM(IF(A$1:A$10&B$1:B$10="AlphaEntity",1,"")),"")+IF(AND(A1<>A2,A1="Charlie"),SUM(IF(A$1:A$10&B$1:B$10="CharlieIndividual",1,""),0))/SUM(IF(A$1:A$10&B$1:B$10="CharlieIndividual",1,""),0)+IFERROR(IF(AND(A1<>A2,A1="Charlie"),SUM(IF(A$1:A$10&B$1:B$10="CharlieEntity",1,""),0)),"")*IFERROR(SUM(IF(A$1:A$10&B$1:B$10="CharlieEntity",1,"")),""))

Upvotes: 0

Related Questions