counta or dcounta should be a simple one

[gunmetal]

So I have this spread sheet/access database where we put our computers in for work, all of that is great but what I want to do is take the main sheet and see how many computers we have done individual from the start until now here is a image of it

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Q Number</td><td style="font-weight: bold;;">Date Completed</td><td style="font-weight: bold;;">Tech</td><td style="font-weight: bold;;">Config_Type</td><td style="font-weight: bold;;">Queue</td><td style="font-weight: bold;;">NW#</td><td style="font-weight: bold;background-color: #FF0000;;">Bill</td><td style="font-weight: bold;background-color: #00B050;;">Totals</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">163838</td><td style="text-align: right;;">7/19/2010 9:37</td><td style=";">Bill</td><td style="text-align: right;;">3</td><td style=";">Tuttle</td><td style="text-align: right;;">949648</td><td style=";">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">163019</td><td style="text-align: right;;">7/19/2010 9:38</td><td style=";">Bill</td><td style="text-align: right;;">3</td><td style=";">SAR-HO</td><td style="text-align: right;;">949650</td><td style=";">2</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">164491</td><td style="text-align: right;;">7/19/2010 9:39</td><td style=";">Bill</td><td style="text-align: right;;">1</td><td style=";">East</td><td style="text-align: right;;">949642</td><td style=";">3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">162974</td><td style="text-align: right;;">7/19/2010 9:53</td><td style=";">Dave</td><td style="text-align: right;;">3</td><td style=";">SAR-HO</td><td style=";">NW947921</td><td style=";">ACT</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">164214</td><td style="text-align: right;;">7/19/2010 9:54</td><td style=";">Dave</td><td style="text-align: right;;">2</td><td style=";">EPCS</td><td style=";">NW947923</td><td style=";">Class 1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">162880</td><td style="text-align: right;;">7/19/2010 9:55</td><td style=";">Dave</td><td style="text-align: right;;">2</td><td style=";">Southwest</td><td style=";">NW947922</td><td style=";">Class 2</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">163840</td><td style="text-align: right;;">7/19/2010 9:56</td><td style=";">Dave</td><td style="text-align: right;;">3</td><td style=";">Tuttle</td><td style=";">NW947918</td><td style=";">Class 3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">163593</td><td style="text-align: right;;">7/19/2010 9:56</td><td style=";">Dave</td><td style="text-align: right;;">2</td><td style=";">West</td><td style=";">NW947928</td><td style="background-color: #FF0000;;">Steve</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">164794</td><td style="text-align: right;;">7/19/2010 9:58</td><td style=";">Dave</td><td style="text-align: right;;">2</td><td style=";">East</td><td style=";">NW947926</td><td style=";">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">164793</td><td style="text-align: right;;">7/19/2010 10:00</td><td style=";">Dave</td><td style="text-align: right;;">2</td><td style=";">East</td><td style=";">NW947925</td><td style=";">2</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">162974</td><td style="text-align: right;;">7/19/2010 10:22</td><td style=";">Dave</td><td style="text-align: right;;">3</td><td style=";">SAR-HO</td><td style=";">NW947921</td><td style=";">3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">160936</td><td style="text-align: right;;">7/19/2010 10:26</td><td style=";">Steve</td><td style="text-align: right;;">2</td><td style=";">Southwest</td><td style="text-align: right;;">953089</td><td style=";">ACT</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">163502</td><td style="text-align: right;;">7/19/2010 10:43</td><td style=";">Steve</td><td style="text-align: right;;">2</td><td style=";">East</td><td style=";">nw953088</td><td style=";">Class 1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">164768</td><td style="text-align: right;;">7/19/2010 10:48</td><td style=";">Steve</td><td style="text-align: right;;">2</td><td style=";">East</td><td style=";">nw953086</td><td style=";">Class 2</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">163030</td><td style="text-align: right;;">7/19/2010 10:49</td><td style=";">Steve</td><td style="text-align: right;;">1</td><td style=";">East</td><td style=";">nw953087</td><td style=";">Class 3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">164492</td><td style="text-align: right;;">7/19/2010 10:55</td><td style=";">Dave</td><td style="text-align: right;;">3</td><td style=";">Tuttle</td><td style=";">NW947927</td><td style="background-color: #FF0000;;">Chris</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">164908</td><td style="text-align: right;;">7/19/2010 12:08</td><td style=";">Bill</td><td style="text-align: right;;">1</td><td style=";">East</td><td style="text-align: right;;">949658</td><td style=";">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">157924</td><td style="text-align: right;;">7/19/2010 12:10</td><td style=";">Bill</td><td style="text-align: right;;">3</td><td style=";">SAR-HO</td><td style="text-align: right;;">949659</td><td style=";">2</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">164769</td><td style="text-align: right;;">7/19/2010 12:11</td><td style=";">Bill</td><td style="text-align: right;;">2</td><td style=";">East</td><td style="text-align: right;;">949656</td><td style=";">3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">164901</td><td style="text-align: right;;">7/19/2010 12:11</td><td style=";">Bill</td><td style="text-align: right;;">1</td><td style=";">East</td><td style="text-align: right;;">949657</td><td style=";">ACT</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">162989</td><td style="text-align: right;;">7/19/2010 12:55</td><td style=";">Dave</td><td style="text-align: right;;">3</td><td style=";">SAR-HO</td><td style=";">NW947920</td><td style=";">Class 1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">164003</td><td style="text-align: right;;">7/19/2010 13:37</td><td style=";">Chris</td><td style="text-align: right;;">1</td><td style=";">East</td><td style=";">NW949446</td><td style=";">Class 2</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">163567</td><td style="text-align: right;;">7/19/2010 13:38</td><td style=";">Chris</td><td style="text-align: right;;">1</td><td style=";">East</td><td style=";">NW949447</td><td style=";">Class 3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">163988</td><td style="text-align: right;;">7/19/2010 13:40</td><td style=";">Chris</td><td style="text-align: right;;">2</td><td style=";">West</td><td style=";">NW949449</td><td style="background-color: #FF0000;;">Yvens</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">164907</td><td style="text-align: right;;">7/19/2010 13:41</td><td style=";">Chris</td><td style="text-align: right;;">1</td><td style=";">East</td><td style=";">NW949448</td><td style=";">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">162185</td><td style="text-align: right;;">7/19/2010 14:08</td><td style=";">Chris</td><td style="text-align: right;;">3</td><td style=";">SAR-HO</td><td style=";">NW949451</td><td style=";">2</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">165375</td><td style="text-align: right;;">7/19/2010 14:20</td><td style=";">Steve</td><td style="text-align: right;;">1</td><td style=";">East</td><td style=";">nw953096</td><td style=";">3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;">163205</td><td style="text-align: right;;">7/19/2010 14:24</td><td style=";">Bill</td><td style="text-align: right;;">2</td><td style=";">West</td><td style="text-align: right;;">949662</td><td style=";">ACT</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">164777</td><td style="text-align: right;;">7/19/2010 14:27</td><td style=";">Steve</td><td style="text-align: right;;">2</td><td style=";">West</td><td style=";">nw953093</td><td style=";">Class 1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;">163836</td><td style="text-align: right;;">7/19/2010 14:28</td><td style=";">Bill</td><td style="text-align: right;;">2</td><td style=";">Southwest</td><td style="text-align: right;;">949638</td><td style=";">Class 2</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">160805</td><td style="text-align: right;;">7/19/2010 14:31</td><td style=";">Steve</td><td style="text-align: right;;">3</td><td style=";">SAR-HO</td><td style=";">nw953082</td><td style=";">Class 3</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

As you can tell its split into different categories, All I am concerned about is
the Tech column "C" and the config_type column "D"

If possible split between 1 2 3 ACT Class 1 Class 2 Class 3

So for example Starting with H2 which would be bills "1" counts it would total 1
H3 would be "2" counts it would total 0
H4 would be "3" and it would total 2

Then it would go to Steve in the same order

 

[cjcobra]

I guess I'm not sure how H3 = 0 and H4 = 2?

 

[gunmetal]

I guess I'm not sure how H3 = 0 and H4 = 2?

Sorry let me do the math again

H2=3
H3=3
H4=4
H5=0
H6-H8=0
H10=2
H11=4

that should clear it up but there are at least 7343 computers built since the start of this excel spreadsheet So I dont need just the answer to the first 32 I would need it for the entire column eg d2:D65536

 

[AlphaFrog]

Put this in H2 and copy it down column H

=COUNTIFS(C:C, INDIRECT("G" & 1+INT(ROW(G1)/8)*8), D:D, G2)

 

[gunmetal]

Put this in H2 and copy it down column H

=COUNTIFS(C:C, INDIRECT("G" & 1+INT(ROW(G1)/8)*8), D:D, G2)

Works in 2007, I am terrible sorry that I forgot to mention that I need this for 2003 and 2007

 

[gunmetal]

I get an #NAME? error

 

[AlphaFrog]

For compatibility with 2003, you could use the SUMPRODUCT function instead of COUNTIFS.

H2
=SUMPRODUCT(--($C$2:$C$15000=G1),--($D$2:$D$15000=G2))

A couple of caveats with SUMPRODUCT

• It will be noticably slower.
• You can't reference an entire column using the syntax C:C, you would have to use something like C2:C65536
• You want to keep the range as small as needed to minimize the overhead. So use a range something like C2:C15000 instead.

Put the above formula in H2 and drag it down to H9. Then edit each those eight formulas so the one red G1 all have G1 in each of the eight formulas. Then copy H2:H9. Select an all the cells below that you want to paste to. Make sure your selection is exactly a factor of eight cells e.g. 16 or 32 or 800 etc. and then paste. The one red G1 will change for every eight cells.