Counting cells with numbers in the column that contains specific words

Hi all,

I’m trying to build a function, but I haven’t been able to successfully navigate some of the eccentricities of Excel formula-building.

Right now I am working on counting all of the cells that contain numbers (so likely just a =COUNT function), but will be eventually getting other descriptive stats as well. My problem is that I would like to create a formula that will perform the counting/mean calculation, etc., but only on a specific column that has the header cell that contains several specific words. Rather than having to type in the target words for each column, I would like to have the target words in the function be imported from multiple designated cells.

For example,

I want to find how many numbered cells are found in column E headed as "dog brown,” but I also have columns headed as "cat brown" "dog black" etc. (which I will be using the formula on later)
to paint a small picture, in my descriptives table, "dog" is in B31 and “brown” is in C30, then “cat” in B32 and “black” in D30.


UPDATE: people are smart and helpful and I have no more worries in this world
Solved by the lovely internet people.xlsx

Hi and welcome to the forum.

Could we have a workbook with some examples and your desired results?

Regards

Maybe with SUMPRODUCT?

Promoting your taste in music, daffodil?

Nice workbook name!

I disagree with your totals as there seem to be 14 participants, not 13, for Task1.

In G22 and copy down:

=COUNT(INDEX($B$2:$E$23,,LOOKUP(2^15,SEARCH(G$21,$B$1:$E$1)*SEARCH($F22,$B$1:$E$1),COLUMN($B$1:$E$1)-MIN(COLUMN($B$1:$E$1))+1)))

In H22 and copy down:

=AVERAGE(INDEX($B$2:$E$23,,LOOKUP(2^15,SEARCH(H$21,$B$1:$E$1)*SEARCH($F22,$B$1:$E$1),COLUMN($B$1:$E$1)-MIN(COLUMN($B$1:$E$1))+1)))

Regards

Re-post then with your attempts at adding those formulas.

Regards

I had no idea it was the name of a band!

I was quoting Johnny Dangerously.


Count

=SUMPRODUCT((ISNUMBER(SEARCH($A26,$B$1:$E$1)))*(ISNUMBER(SEARCH(B$25,$B$1:$E$1)))*($B$2:$E$18<>""))

Mean

=SUMPRODUCT((ISNUMBER(SEARCH($A26,$B$1:$E$1)))*(ISNUMBER(SEARCH(C$25,$B$1:$E$1)))*($B$2:$E$18))/
SUMPRODUCT((ISNUMBER(SEARCH($A26,$B$1:$E$1)))*(ISNUMBER(SEARCH(C$25,$B$1:$E$1)))*($B$2:$E$18<>""))



Edit: INDEX with a nexted LOOKUP? Mind = Blown

Is this thread trying to win the prize for "Most Creative Workbook Names" or what??