complicated count

[gwiz]

Ok, I have a large range of data of basically random numbers. I want to count the occurances of the different numbers when a certain number pops up. Let me give an example:

I have these values (see example2.jpg) and I want this output.

So, for the rows that contain a "1" then it counts, if any, how many of the other numbers are in that row. In this case, there are 2 "2's", 4 "3's", 2 "4's", 1 "5", and 1 "6".

Thanks for ya'lls help!

[duane]

with your data in cells a1:c7 and 2 through 6 being in cells a10:a14

in cell b10 and copied down through b14

=IF(NOT(ISERROR(MATCH(1,$A$1:$C$1,0))),COUNTIF($A$1:$C$1,$A10),0)+IF(NOT(ISERROR(MATCH(1,$A$2:$C$2,0))),COUNTIF($A$2:$C$2,$A10),0)+IF(NOT(ISERROR(MATCH(1,$A$3:$C$3,0))),COUNTIF($A$3:$C$3,$A10),0)+IF(NOT(ISERROR(MATCH(1,$A$4:$C$4,0))),COUNTIF($A$4:$C$4,$A10),0)+IF(NOT(ISERROR(MATCH(1,$A$5:$C$5,0))),COUNTIF($A$5:$C$5,$A10),0)+IF(NOT(ISERROR(MATCH(1,$A$6:$C$6,0))),COUNTIF($A$6:$C$6,$A10),0)+IF(NOT(ISERROR(MATCH(1,$A$7:$C$7,0))),COUNTIF($A$7:$C$7,$A10),0)

[duane]

upon more thought, this more simpel formula seems to do the trick, and could more easily be extended to additional rows. (in row 10 and copied down per my other post)

=SUMPRODUCT(($A$1:$A$7=1)*($A$1:$C$7=$A11))+SUMPRODUCT(($B$1:$B$7=1)*($A$1:$C$7=$A11))+SUMPRODUCT(($C$1:$C$7=1)*($A$1:$C$7=$A11))

[gwiz]

That's exactly what I want! At first, I didn't even know exactly what I wanted, but that formula does exactly what I want! Thanks!

[daddylonglegs]

Given your screenshot example you could use this formula in G2 copied down and across to L8

=IF($F2=G$1,"",SUMPRODUCT((($A$2:$A$8=$F2)+($B$2:$B$8=$F2)+($C$2:$C$8=$F2)>0)*($A$2:$C$8=G$1)))