CODEWITHSUNDEEP
excelexcel-formula
Jack Armstrong
Jack Armstrong

Reputation: 1249

Count Text values, but not repeats

To be honest, I don't even know what I am asking, but I think it should be possible. I am trying to count the number of groupings for a certain person given a specific condition (T/F).

For example, Let's assume these are all true

Orange   Alpha
Orange   Alpha
Black    Beta
Black    Beta
Blue     Beta
Blue     Beta
Blue     Beta
Green    Delta
Green    Alpha

I am trying to create a table that will say Beta: 2, Alpha 2, and Delta 1. Then vice versa I need something that will say Orange 1, Black 1, Blue 1, Green 2. Essentially count the number of names within in a color.

Thank you

Upvotes: 0

Views: 73

Answers (1)

Scott Craner
Scott Craner

Reputation: 152485

For formula you will need to use Array formulas:

For the Alpha column:

=SUM(IF($B$1:$B$9=D1,1/COUNTIFS($A$1:$A$9,$A$1:$A$9,$B$1:$B$9,D1)))

For the Color column:

=SUM(IF($A$1:$A$9=G1,1/COUNTIFS($B$1:$B$9,$B$1:$B$9,$A$1:$A$9,G1)))

As these are array formulas they need to be confirmed with Ctrl-Shift-Enter on exiting edit mode instead of Enter. If done properly then Excel will put {} around the formula.

enter image description here

Upvotes: 2

Related Questions