Conditional Counting

R

raymaster98

Board Regular
Joined
Oct 28, 2009
Messages
212
Using Excel 2010.

In column F of a sheet titled "Record Review", I have a list of numbers (they are all 5 digits).

In column K of a sheet titled "Customer Service Classes Report", I have an imported list. In this list, the same numbers (5 digits) from "Record Review" are preceded by the words "Emp ID:".

In cell O3 of "Record Review", I need a formula that will match the number from column F in "Record Review" with the last 5 digits of the alpha/number combo in column K of "Customer Service Classes Report" AND (here's the important part) return the number of non blank cells in the row 2 rows under the matched cell from column K of "Customer Service Classes Report".

Whew! Hope I explained it well enough

Any help would be greatly appreciated. Thanks, Kenny
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Maybe this:

Note: I used array formula - entered with Ctrl+Shift+Enter and not only Enter.

<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 /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #C5D9F1;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #C5D9F1;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">27</td><td style="text-align: center;;">40</td><td style="text-align: center;;">57</td><td style="text-align: center;;">82</td><td style="text-align: center;;">74</td><td style="text-align: center;background-color: #C5D9F1;;">Emp ID:12361</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">56</td><td style="text-align: center;background-color: #C5D9F1;;">Emp ID:12345</td><td style="text-align: center;;"></td><td style="text-align: center;;">20</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;">67</td><td style="text-align: center;;">48</td><td style="text-align: center;;">50</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #C5D9F1;;">Emp ID:12360</td><td style="text-align: center;;">59</td><td style="text-align: center;;"></td><td style="text-align: center;;">46</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">89</td><td style="text-align: center;;">13</td><td style="text-align: center;;">16</td><td style="text-align: center;background-color: #C5D9F1;;">Emp ID:12359</td><td style="text-align: center;;">30</td><td style="text-align: center;;"></td><td style="text-align: center;;">60</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #C5D9F1;;">Emp ID:12354</td><td style="text-align: center;;">34</td><td style="text-align: center;;">15</td><td style="text-align: center;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">76</td><td style="text-align: center;;">72</td><td style="text-align: center;;">39</td><td style="text-align: center;background-color: #C5D9F1;;">Emp ID:12355</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">49</td><td style="text-align: center;;">88</td><td style="text-align: center;;">85</td><td style="text-align: center;;">85</td><td style="text-align: center;;">55</td><td style="text-align: center;background-color: #C5D9F1;;">Emp ID:12358</td><td style="text-align: center;;">71</td><td style="text-align: center;;">70</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">12</td><td style="text-align: center;;">61</td><td style="text-align: center;background-color: #C5D9F1;;">Emp ID:12348</td><td style="text-align: center;;">17</td><td style="text-align: center;;">66</td><td style="text-align: center;;">78</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">59</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #C5D9F1;;">Emp ID:12352</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">45</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">57</td><td style="text-align: center;;">24</td><td style="text-align: center;;">56</td><td style="text-align: center;;"></td><td style="text-align: center;;">71</td><td style="text-align: center;background-color: #C5D9F1;;">Emp ID:12351</td><td style="text-align: center;;">21</td><td style="text-align: center;;"></td><td style="text-align: center;;">83</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;"></td><td style="text-align: center;;">77</td><td style="text-align: center;;"></td><td style="text-align: center;;">87</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #C5D9F1;;">Emp ID:12357</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">16</td><td style="text-align: center;;">55</td><td style="text-align: center;;">54</td><td style="text-align: center;background-color: #C5D9F1;;">Emp ID:12364</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">82</td><td style="text-align: center;;">29</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">56</td><td style="text-align: center;background-color: #C5D9F1;;">Emp ID:12363</td><td style="text-align: center;;"></td><td style="text-align: center;;">11</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">81</td><td style="text-align: center;;">23</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #C5D9F1;;">Emp ID:12353</td><td style="text-align: center;;">21</td><td style="text-align: center;;"></td><td style="text-align: center;;">83</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">80</td><td style="text-align: center;;">17</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">56</td><td style="text-align: center;background-color: #C5D9F1;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">79</td><td style="text-align: center;;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #C5D9F1;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">78</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">56</td><td style="text-align: center;background-color: #C5D9F1;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">12</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #C5D9F1;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td></tr></tbody></table><p style="width:18.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">Customer Service Classes Report</p><br /><br /><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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>F</th><th>G</th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;background-color: #C5D9F1;;">DataF</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">No Blanks</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;background-color: #C5D9F1;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;background-color: #C5D9F1;;">12361</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;background-color: #C5D9F1;;">12358</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;background-color: #C5D9F1;;">12359</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;background-color: #C5D9F1;;">12346</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;background-color: #C5D9F1;;">12356</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;background-color: #C5D9F1;;">12355</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;background-color: #C5D9F1;;">12364</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;background-color: #C5D9F1;;">12363</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;background-color: #C5D9F1;;">12349</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;background-color: #C5D9F1;;">12354</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;background-color: #C5D9F1;;">12360</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;background-color: #C5D9F1;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td></tr></tbody></table><p style="width:7.8em;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">Record Review</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O3</th><td style="text-align:left">{=IF(<font color="Blue">SUM(<font color="Red">--ISNUMBER(<font color="Green">FIND(<font color="Purple">F3,'Customer Service Classes Report'!K$3:K$19</font>)</font>)</font>),COLUMNS(<font color="Red">'Customer Service Classes Report'!$1:$1</font>)-COUNTBLANK(<font color="Red">INDEX(<font color="Green">'Customer Service Classes Report'!$1:$19,
MAX(<font color="Purple">--IF(<font color="Teal">ISNUMBER(<font color="#FF00FF">FIND(<font color="Navy">F3,'Customer Service Classes Report'!K$3:K$16</font>)</font>),ROW(<font color="#FF00FF">'Customer Service Classes Report'!K$3:K$16</font>)</font>)</font>)+2,0</font>)</font>),0</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz
 
Upvote 0
raymaster98 said:
Using Excel 2010.

In column F of a sheet titled "Record Review", I have a list of numbers (they are all 5 digits).

In column K of a sheet titled "Customer Service Classes Report", I have an imported list. In this list, the same numbers (5 digits) from "Record Review" are preceded by the words "Emp ID:".

In cell O3 of "Record Review", I need a formula that will match the number from column F in "Record Review" with the last 5 digits of the alpha/number combo in column K of "Customer Service Classes Report" AND (here's the important part) return the number of non blank cells in the row 2 rows under the matched cell from column K of "Customer Service Classes Report".

Whew! Hope I explained it well enough

Any help would be greatly appreciated. Thanks, Kenny
Click to expand...
OK, that should not be a problem.

However, what is the exact range that should be counted?

Let's assume we find a match in cell K2. 2 rows below that would be row 4. So, what is the range on row 4 that we should be counting?
 
Upvote 0
The formula that markmzz posted works great. However, I need to be able to copy the formula down the column for it to be perfect. BTW...T. Valko, I need to count all non blank cells in the row (row 4 in your example).

Thanks for your replies, Kenny
 
Upvote 0
OK. Now I know I am pushing my luck, but here goes...

Using markmzz's formula, is there a way to edit the formula so that all non blank values in column G would be counted as 1 and then multiplied by 3. 3 would then need to be added to the rest of the non blank entries in the row. If correct, the returned values for the following numbers would be:
12361 9
12354 11
12348 10
12352 6
12364 8

Thanks for your help. Kenny
 
Upvote 0
raymaster98 said:
OK. Now I know I am pushing my luck, but here goes...

Using markmzz's formula, is there a way to edit the formula so that all non blank values in column G would be counted as 1 and then multiplied by 3. 3 would then need to be added to the rest of the non blank entries in the row. If correct, the returned values for the following numbers would be:
12361 9
12354 11
12348 10
12352 6
12364 8

Thanks for your help. Kenny
Click to expand...

Maybe this:

<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>F</th><th>G</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;background-color: #C5D9F1;;">DataF</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">No Blanks</td><td style="text-align: center;background-color: #FFFF00;;">No Blanks-with G*3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;background-color: #C5D9F1;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;"></td><td style="text-align: center;background-color: #FFFF00;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;background-color: #C5D9F1;;">12361</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">7</td><td style="text-align: center;background-color: #FFFF00;;">10</td><td style="text-align: center;;">12361</td><td style="text-align: center;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;background-color: #C5D9F1;;">12354</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">9</td><td style="text-align: center;background-color: #FFFF00;;">12</td><td style="text-align: center;;">12354</td><td style="text-align: center;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;background-color: #C5D9F1;;">12348</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">8</td><td style="text-align: center;background-color: #FFFF00;;">11</td><td style="text-align: center;;">12348</td><td style="text-align: center;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;background-color: #C5D9F1;;">12352</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">4</td><td style="text-align: center;background-color: #FFFF00;;">7</td><td style="text-align: center;;">12352</td><td style="text-align: center;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;background-color: #C5D9F1;;">12364</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">6</td><td style="text-align: center;background-color: #FFFF00;;">9</td><td style="text-align: center;;">12364</td><td style="text-align: center;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;background-color: #C5D9F1;;">12363</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">4</td><td style="text-align: center;background-color: #FFFF00;;">7</td><td style="text-align: center;;">12363</td><td style="text-align: center;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;background-color: #C5D9F1;;">12357</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">6</td><td style="text-align: center;background-color: #FFFF00;;">9</td><td style="text-align: center;;">12357</td><td style="text-align: center;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;background-color: #C5D9F1;;">12353</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">2</td><td style="text-align: center;background-color: #FFFF00;;">5</td><td style="text-align: center;;">12353</td><td style="text-align: center;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;background-color: #C5D9F1;;">12358</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">3</td><td style="text-align: center;background-color: #FFFF00;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;background-color: #C5D9F1;;">12359</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">4</td><td style="text-align: center;background-color: #FFFF00;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;background-color: #C5D9F1;;">12346</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;background-color: #C5D9F1;;">12356</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;background-color: #C5D9F1;;">12355</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">6</td><td style="text-align: center;background-color: #FFFF00;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;background-color: #C5D9F1;;">12349</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;background-color: #C5D9F1;;">12360</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">4</td><td style="text-align: center;background-color: #FFFF00;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td></tr></tbody></table><p style="width:7.8em;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">Record Review</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O3</th><td style="text-align:left">{=IF(<font color="Blue">SUM(<font color="Red">--ISNUMBER(<font color="Green">FIND(<font color="Purple">F3,EMPID</font>)</font>)</font>),COLUMNS(<font color="Red">'Customer Service Classes Report'!$1:$1</font>)-COUNTBLANK(<font color="Red">INDEX(<font color="Green">'Customer Service Classes Report'!$1:$19,
MAX(<font color="Purple">--IF(<font color="Teal">ISNUMBER(<font color="#FF00FF">FIND(<font color="Navy">F3,EMPID</font>)</font>),ROW(<font color="#FF00FF">EMPID</font>)</font>)</font>)+2,0</font>)</font>),0</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">P3</th><td style="text-align:left">{=IF(<font color="Blue">SUM(<font color="Red">--ISNUMBER(<font color="Green">FIND(<font color="Purple">F3,EMPID</font>)</font>)</font>),COLUMNS(<font color="Red">'Customer Service Classes Report'!$1:$1</font>)-COUNTBLANK(<font color="Red">INDEX(<font color="Green">'Customer Service Classes Report'!$1:$19,
MAX(<font color="Purple">--IF(<font color="Teal">ISNUMBER(<font color="#FF00FF">FIND(<font color="Navy">F3,EMPID</font>)</font>),ROW(<font color="#FF00FF">EMPID</font>)</font>)</font>)+2,0</font>)</font>)+3*(<font color="Red">1-COUNTBLANK(<font color="Green">INDEX(<font color="Purple">'Customer Service Classes Report'!G$1:G$19,
MAX(<font color="Teal">--IF(<font color="#FF00FF">ISNUMBER(<font color="Navy">FIND(<font color="Blue">F3,EMPID</font>)</font>),ROW(<font color="Navy">EMPID</font>)</font>)</font>)+2,0</font>)</font>)</font>),0</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">EMPID</th><td style="text-align:left">='Customer Service Classes Report'!$K$3:INDEX(<font color="Blue">'Customer Service Classes Report'!$K:$K,MATCH(<font color="Red">REPT(<font color="Green">"z",9</font>),'Customer Service Classes Report'!$K:$K,1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Do some tests and tell us if it work.

Markmzz
 
Last edited:
Upvote 0
raymaster98 said:
OK. Now I know I am pushing my luck, but here goes...

Using markmzz's formula, is there a way to edit the formula so that all non blank values in column G would be counted as 1 and then multiplied by 3. 3 would then need to be added to the rest of the non blank entries in the row. If correct, the returned values for the following numbers would be:
12361 9
12354 11
12348 10
12352 6
12364 8

Thanks for your help. Kenny
Click to expand...

Would you post a small sample reflecting your own lay-out along with the desired results?
 
Upvote 0
raymaster98 said:
T. Valko, I need to count all non blank cells in the row (row 4 in your example).

Thanks for your replies, Kenny
Click to expand...
Well, it would have helped if you had told us the SPECIFIC range to count. Row 4 can be more than 16,000 cells depending on what version of Excel you're using.

So, this will count the ENTIRE row...

Assuming the range of interest is row 1 to row 100.

Array entered**:

=COUNTIF(INDEX('Customer Service Classes Report'!$1:$100,MATCH(F2&"",RIGHT('Customer Service Classes Report'!K$1:K$100,5),0)+2,0),"<>")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Matching TODAY() using VLOOKUP
Replies
7
Views
246
Peter_SSs
Peter_SSs
S
  • Question Question
Value lookup formula
Replies
3
Views
206
Joe4
Joe4
montecarlo2012
  • Question Question
change display from horizontal to vertical
Replies
2
Views
134
montecarlo2012
montecarlo2012
Dan Wilson
  • Solved
Help with the Random Function
Replies
7
Views
548
Fluff
Fluff
youbitto
  • Solved
Index match VBA taking too long
2 3
Replies
27
Views
598
Alex Blakenburg
A

Forum statistics

Threads
1,224,525
Messages
6,179,317
Members
452,905
Latest member
deadwings

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top