multicondition summing/counting using cell references

[benhmckelvey]

I would like to COUNT (could be SUM) the number of instances where column A = $D$1 while column B = $E$1. So going by the table below the answer should be 3. I found I can do it when I insert "D-2" into a SUMPRODUCT formula but not when I refer to cell $D$1. If I can help it I would like to refrain from using a helper column. Any ideas guys? Cheers Ben

<table style="border-collapse: collapse; width: 269pt;" width="358" border="0" cellpadding="0" cellspacing="0"><col style="width: 29pt;" width="38"> <col style="width: 48pt;" span="5" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; width: 29pt;" width="38" height="20">
</td> <td class="xl68" style="width: 48pt;" width="64">A</td> <td class="xl68" style="width: 48pt;" width="64">B</td> <td class="xl68" style="width: 48pt;" width="64">C</td> <td class="xl68" style="width: 48pt;" width="64">D</td> <td class="xl68" style="width: 48pt;" width="64">E</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">1</td> <td>D-2</td> <td class="xl66">D-2</td> <td>
</td> <td>D-2</td> <td>D-3</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">2</td> <td>D-2</td> <td class="xl66">D-3</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">3</td> <td>D-2</td> <td class="xl66">D-4</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">4</td> <td>D-2</td> <td class="xl66">D-3</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">5</td> <td>D-2</td> <td class="xl66">D-4</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">6</td> <td>D-2</td> <td class="xl66">D-3</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">7</td> <td class="xl66">D-3</td> <td class="xl66">D-4</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">8</td> <td class="xl66">D-3</td> <td class="xl66">D-3</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">9</td> <td class="xl66">D-3</td> <td class="xl66">D-2</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">10</td> <td class="xl66">D-3</td> <td class="xl66">D-2</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">11</td> <td class="xl66">D-3</td> <td class="xl66">D-4</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>

 

[AdamL]

You might want to post the formula you tried Ben, because I can't see why

=SUMPRODUCT(--($A$1:$A$11=$D$1),--($B$1:$B$11=$E$11))

wouldn't work.

 

[benhmckelvey]

Thanks Adam

The 2 formulas I was using was

=SUMPRODUCT(($A:$A=$D$1)*($B:$B=$E$1))
and
{=SUM(($A:$A=$D$1)*($B:$B=$E$1))}

Where the 2nd formula would work but it was hard-coated in.

I haven't used the double negative before but it works a treat

 

[AdamL]

No problem Ben.

I just wonder if you're using pre-Excel 2007, in which case whole columns do not work with SUMPRODUCT (I think).

Cheers
Adam

 

[PaddyD]

"...(I think)..."

You are correct. Best to only calculate over the data that's actually being used.