Search code examples
excelexcel-formulacountcountif

Excel - Count Number of Rows Value X Appears In Where Column A Equals Y

I have the following dataset which is trimmed down here. It has about 3K rows where it repeats the same section over and over, but the values are slightly different. The colors repeat and never change, but the values in B:G may change for each color.

I am attempting to count the number of rows a value, such as Oscar, appears in, for each color. For the example dataset here, Oscar would receive the value of 1 for the color Red because of the two rows where Red is the color, he appears in only one of those rows. Likewise, he would receive a 2 for the color Purple because he appears in 2 rows that are the color Purple.

The difficulty I am having is I keep falling back to doing a count of the number of times the name appears for a color. Closest I've got is this below where K22:K30 are a list of colors and Q21:30 are the names.

=COUNT(IF(FILTER($A:$G,$K22=$A:$A)=Q$21,1,""))

Any help is appreciated, thanks.

A B C D E F G
Blue John Brian Steve Dale Brady Lane
Green Steve Dale Kyle Kyle Kyle Kyle
Red Steve James James James Dale Dale
Yellow John Brian Brian Brian John John
Orange John Peter Dave Dave Dave Dave
Purple John Peter Peter Peter Oscar Oscar
Pink Dale Steve Oscar Oscar Oscar Oscar
Black Mike Mike Mike Mike Dale Kyle
Cyan Austin Austin Austin Dale Steve Steve
Blue John Brian Steve Dale Brady Lane
Green Steve Dale Kyle Kyle Kyle Kyle
Red Oscar Oscar Oscar Oscar Oscar Oscar
Yellow John Brian Brian Brian John John
Orange John Peter Dave Dave Dave Dave
Purple John Peter Peter Peter Oscar Oscar
Pink Dale Steve Oscar Oscar Oscar Oscar
Black Mike Mike Mike Mike Dale Kyle
Cyan Austin Austin Austin Dale Steve Steve
Solution

  • Edit: This seems like you are looking for the following counts, if I am not mistaken here: (FILTER() function is redundant not needed, I followed the formula in OP, so realized later)

    enter image description here

    • Method 1: Using MMULT()

    =SUM(--(MMULT(($I3=$B$1:$G$18)*(J$2=$A$1:$A$18),SEQUENCE(COLUMNS($B$1:$G$1)))>0))

    • Method 2: Using BYROW() with LAMBDA() construct

    =SUM(N(BYROW(($I3=$B$1:$G$18)*(J$2=$A$1:$A$18),LAMBDA(x,OR(x)))))

    • Method 3: Using BYROW() with ETA LAMBDA construct

    =SUM(N(BYROW(($I3=$B$1:$G$18)*(J$2=$A$1:$A$18),OR)))

    You can also use MAKEARRAY() to return one single dynamic array formula:

    enter image description here

    =LET(
     _Data, A1:G18,
     _Colors, TAKE(_Data,,1),
     _Names, DROP(_Data,,1),
     _UniqC, TOROW(UNIQUE(_Colors)),
     _UniqN, UNIQUE(TOCOL(_Names)),
     _Counts, MAKEARRAY(ROWS(_UniqN),COLUMNS(_UniqC), LAMBDA(r,c,
             SUM(--(MMULT((INDEX(_UniqN,r)=_Names)*(INDEX(_UniqC,c)=_Colors),SEQUENCE(COLUMNS(_Data)-1))>0)))),
     HSTACK(VSTACK("",_UniqN), VSTACK(_UniqC, _Counts)))