CODEWITHSUNDEEP
excelexcel-formulacountifsumproduct
llewmihs
llewmihs

Reputation: 51

Automatically counting rows based on two changing criterion

I have a dataset that has in it collections of 3 attributes from a wider pool of 16. I want to build a heat map that shows which attributes are more or less often connected to one another.

Currently my data is in the format (note this is a sample from a larger set of 70 rows):

Image of the source data

I would like to be able to automatically populate the following table:

The table that needs populating

Is there a way that I can automate this process in excel, instead of manually counting each time two attributes are mentioned in the same set of three? I've seen some examples using SUMPRODUCT, but these examples seem to required each column to have a specific type of data in, whereas in my example all 3 columns could contain any of the attributes.

Upvotes: 1

Views: 60

Answers (2)

Ike
Ike

Reputation: 13024

Slightly extended/different approach than Tom Sharpe:

  • It fills the whole matrix by making use of MAKEARRAY with horizontal and vertical attributes ranges as input.
  • using double-negative to return the COUNTIF-conditions
  • It excludes same-same-attributes (e.g. in my example a - a --> returns 0) ("last condition" (INDEX(attrH,,c)<>INDEX(attrV,r))
=LET(attrH,B8:F8,
attrV,A9:A13,
MAKEARRAY(ROWS(attrV),COLUMNS(attrH),LAMBDA(r,c,
          SUM(BYROW(tblData,LAMBDA(row,
                      --(COUNTIF(row,INDEX(attrH,,c))*COUNTIF(row,INDEX(attrV,r))
                        * (INDEX(attrH,,c)<>INDEX(attrV,r)))))))))

enter image description here

Upvotes: 1

Tom Sharpe
Tom Sharpe

Reputation: 34230

As you have Excel 365, you can use a Lambda with Byrow to get the row totals for the appearance of one word of a pair, then multiply by the totals for the other word to get the totals for the occurrence of both words. Add the resulting array to get the number of rows in which both words occur and drag down and across:

=SUM(BYROW($A$2:$C$6,LAMBDA(array,COUNTIF(array,$F3)))*BYROW($A$2:$C$6,LAMBDA(array,COUNTIF(array,G$2))))

enter image description here

Assumes an attribute can't occur more than once in a row.

As noted here, Countif can be used in this situation although it takes a range rather than an array.

Upvotes: 2

Related Questions