COUNTIF with Discontinuous Ranges

[Dabbler_13579]

Hello. I'm working with a spreadsheet where my data has some formatting constraints. Because of this, I'm trying to work with some ranges that are discontinuous. Now functions like FREQUENCY and SMALL don't mind if I feed them a defined name like RANGE_01=A1:B2,A4:B5. SMALL(RANGE_01,1) works just fine, but if I try to do COUNTIF(RANGE_01,0) I get a #VALUE error. I tried splitting RANGE_01 up into RANGE_02=A1:B2 and RANGE_03=A4:B5 then doing COUNTIF(RANGE_02:RANGE_03,0), but this works out as COUNTIF(A1:B5,0). So far the only work-arounds I've figured out are to do COUNTIF(RANGE_02,0)+COUNTIF(RANGE_03,0) (except repeated many times because my actual range has more than two parts) or copying A1:B2 to C1:D2 and A4:B5 to C3:D4 and doing COUNTIF(C1:D4,0). I don't like the first one because it's a pain in the butt and a lot of typing. I don't like the second one because it's inelegant-I feel like there is or should be a way to do this without using those extra cells. Can anyone give me some ideas? I saw something about using INDIRECT to work around this, but I couldn't figure it out. Thanks!

 

[XOR LX]

Hi,

=SUM(COUNTIF(INDIRECT({"A1:B2","A4:B5"}),0))

Regards

 

[Dabbler_13579]

http://1drv.ms/1kgBb0p

Thanks for your help. I don't understand why we're SUMming over the COUNTIF or why there are brackets in the INDIRECT. Regardless, that answers the question I asked, but I wasn't able to generalize it to my actual problem (my fault for not asking my whole question in the first place). The link at the top should let you download my workbook and see what I'm doing. I apologize for not putting the table directly in my post, but I'm having horrible problems with this forum software.

 

[Aladin Akyurek]

What is the actual outcome you want to see in H2:H23? Please do not repeat yourself: just say what are the values that must appear appear in that range?

 

[Dabbler_13579]

What is the actual outcome you want to see in H2:H23?

{0;9;10;11;12;13;14;15;16;18;20;22;24;26;27;28;30;32;34;36;45;54}

 

[Aladin Akyurek]

{0;9;10;11;12;13;14;15;16;18;20;22;24;26;27;28;30;32;34;36;45;54}

Thanks for the concise and clear specification...


[TABLE="width: 181"]
<COLGROUP><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 512" span=2 width=14><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" span=5 width=22><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2133" width=60><COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1621" width=46><TBODY>[TR]
[TD="class: xl63, width: 14, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl64, width: 14, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl64, width: 22, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl64, width: 22, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl64, width: 22, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl64, width: 22, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl64, width: 22, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, width: 60, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl65, width: 46, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]54[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent"]Number[/TD]
[TD="class: xl65, bgcolor: transparent"]Count[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]91[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]27[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]27[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]30[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]32[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]30[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]34[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]36[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]45[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]54[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]36[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]32[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]45[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]32[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]32[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]34[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]30[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]32[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]32[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]34[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]30[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]32[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]34[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]54[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

H1, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY((B2:G7,B10:G15,B18:G23,B26:G31,B34:G39,B42:G47),
  ROW(INDIRECT(H2+1&":"&H3+1))-1),1))

H2, just enter:

=MIN(B2:G7,B10:G15,B18:G23,B26:G31,B34:G39,B42:G47)

H3, just enter:

=MAX(B2:G7,B10:G15,B18:G23,B26:G31,B34:G39,B42:G47)

H5, control+shift+enter, not just enter, and copy down:

=IF(ROWS($H$5:H5)<=$H$1,
  SMALL(IF(FREQUENCY(($B$2:$G$7,$B$10:$G$15,$B$18:$G$23,
   $B$26:$G$31,$B$34:$G$39,$B$42:$G$47),ROW(INDIRECT($H$2+1&":"&$H$3+1))-1),
  ROW(INDIRECT($H$2+1&":"&$H$3+1))-1),ROWS($H$5:H5)),"")

I5, control+shift+enter, not just enter, and copy down:

=IF($H5="","",
  INDEX(FREQUENCY(($B$2:$G$7,$B$10:$G$15,$B$18:$G$23,
   $B$26:$G$31,$B$34:$G$39,$B$42:$G$47),CHOOSE({1,2},$H5-1,$H5)),2))