Look up by 2 values

[currybai]

I have the following columns,

Goal Status which is either Not at Goal or At Goal
Run Status which is either Behind or Ahead

I want to do a summary table like :

<table border="0" cellpadding="0" cellspacing="0" width="221"><col style="width: 78pt;" width="104"> <col style="width: 50pt;" width="67"> <col style="width: 38pt;" width="50"> <tbody><tr style="height: 12pt;" height="16"> <td class="xl65" style="height: 12pt; width: 78pt;" width="104" height="16">Overall</td> <td class="xl65" style="border-left: medium none; width: 50pt;" width="67">Pacing</td> <td class="xl65" style="border-left: medium none; width: 38pt;" width="50">
</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl66" style="height: 12pt; border-top: medium none;" height="16">Not At Goal</td> <td class="xl67" style="border-top: medium none;">Behind</td> <td class="xl68" style="border-top: medium none;"> </td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl69" style="height: 12pt;" height="16"> </td> <td class="xl70">Ahead</td> <td class="xl71"> </td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl66" style="height: 12pt; border-top: medium none;" height="16">At Goal</td> <td class="xl67" style="border-top: medium none;">Behind</td> <td class="xl68" style="border-top: medium none;"> </td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl69" style="height: 12pt;" height="16"> </td> <td class="xl70">Ahead</td> <td class="xl71"> </td> </tr> </tbody></table>I basically want the number of not at goal and behind, not at goal and ahead, etc

 

[c_m_s_jr]

Have you considering using a pivot table? It sounds me like that would work the best.

 

[currybai]

Have you considering using a pivot table? It sounds me like that would work the best.

I tried but I cant get it to look like how I want it to look like because the Overall portion is 1 column, and I also want to show if it hit goal in the last month so I have a separate column for Last month. So really it looks like


<table border="0" cellpadding="0" cellspacing="0" width="221"><tbody><tr style="height: 12pt;" height="16"><td class="xl65" style="height: 12pt; width: 78pt;" width="104" height="16"> <table border="0" cellpadding="0" cellspacing="0" width="524"><col style="width: 69pt;" width="92" span="5"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12pt;" height="16"> <td style="height: 12pt; width: 69pt;" width="92" height="16">
</td> <td style="width: 69pt;" width="92">
</td> <td style="width: 69pt;" width="92">
</td> <td style="width: 69pt;" width="92">
</td> <td style="width: 69pt;" width="92">
</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 12pt;" height="16"> <td style="height: 12pt;" height="16">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12pt;" height="16"> <td style="height: 12pt;" height="16">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12pt;" height="16"> <td style="height: 12pt;" height="16">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12pt;" height="16"> <td style="height: 12pt;" height="16">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table></td> <td class="xl65" style="border-left: medium none; width: 50pt;" width="67">
</td> <td class="xl65" style="border-left: medium none; width: 38pt;" width="50">
</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl66" style="height: 12pt; border-top: medium none;" height="16">
</td> <td class="xl67" style="border-top: medium none;">
</td> <td class="xl68" style="border-top: medium none;">
</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl69" style="height: 12pt;" height="16">
</td> <td class="xl70">
</td> <td class="xl71">
</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl66" style="height: 12pt; border-top: medium none;" height="16">
</td> <td class="xl67" style="border-top: medium none;">
</td> <td class="xl68" style="border-top: medium none;">
</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl69" style="height: 12pt;" height="16">


</td> <td class="xl70">
</td></tr></tbody></table> <table border="0" cellpadding="0" cellspacing="0" width="894"><col style="width: 112pt;" width="149" span="6"> <tbody><tr style="height: 12pt;" height="16"> <td style="height: 12pt; width: 112pt;" width="149" height="16">Overall</td> <td style="width: 112pt;" width="149">Pacing</td> <td style="width: 112pt;" width="149">#</td> <td style="width: 112pt;" width="149">Last Month</td> <td style="width: 112pt;" width="149">Pacing</td> <td style="width: 112pt;" width="149">#</td> </tr> <tr style="height: 12pt;" height="16"> <td style="height: 12pt;" height="16">Not at goal</td> <td>ahead</td> <td>
</td> <td>Not at goal</td> <td>ahead</td> <td>
</td> </tr> <tr style="height: 12pt;" height="16"> <td style="height: 12pt;" height="16">
</td> <td>behind</td> <td>
</td> <td>
</td> <td>behind</td> <td>
</td> </tr> <tr style="height: 12pt;" height="16"> <td style="height: 12pt;" height="16">at goal</td> <td>ahead</td> <td>
</td> <td>at goal</td> <td>ahead</td> <td>
</td> </tr> <tr style="height: 12pt;" height="16"> <td style="height: 12pt;" height="16">
</td> <td>behind</td> <td>
</td> <td>
</td> <td>behind</td> <td>
</td> </tr> </tbody></table>

 

[c_m_s_jr]

Not knowing your column headings you could try a SUMPRODUCT:

=SUMPRODUCT(--(RANGE="Not a goal"),--(RANGE="Ahead"))

Where the first RANGE is the range of cells that contain "Not a goal" or "at goal"
Where the second Range is the range of cells that contain "ahead" or "behind"

Effectively what SUMPRODUCT will return is a count of the number of matches to the criteria.