SUM function logical conditions not resolving to 1

M

MIsim

New Member
Joined
Jul 16, 2011
Messages
6
Hello All, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
The following formula is in 6 ranges of cells that were working fine until I updated the sheet to include new numbers coming in the data (to coincide with the 'DATA-INPUT'!$D$2:$D$4899=$Bn range of cells- where n= 7 - 27 ).<o:p></o:p>
<o:p></o:p>
=SUM(('DATA-INPUT'!$B$2:$B$4899="Current")*('DATA-INPUT'!$A$2:$A$4899=$C$4)*('DATA-INPUT'!$C$2:$C$4899=CHANNEL)*('DATA-INPUT'!$G$2:$G$4899=F$4)*('DATA-INPUT'!$D$2:$D$4899=$B7)*('DATA-INPUT'!$H$2:$H$4899))
<o:p></o:p>
<o:p></o:p>
$C$4="F" CHANNEL="JIM" F$4="APPS" or "SMITH" $B7=26
<o:p></o:p>
<o:p></o:p>
Except for the last section of the formula all but the last section of the formula resolve to "1" resulting in sum(1*1*1*1*1*)*('DATA-INPUT'!$H$2:$H$4899)) so summing those cells in the range where the criteria in the preceding cell ranges in the formula are being met. I separated the formula into its various components and all bar the column "G" resolved to "1".
<o:p></o:p>
<o:p></o:p>
My question is how can I make the formula resolve correctly for ALL the logical conditions as it was before I added the new numbers in the $Bn range ?? <o:p></o:p>
<o:p></o:p>
numbers added <o:p></o:p>
I<o:p></o:p>
V<o:p></o:p>
There is a sample of the data below ... V<o:p></o:p>
A B C D G H
<TABLE style="WIDTH: 384pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 0cm 0cm 0cm" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=512 u1:str><COLGROUP><COL style="WIDTH: 48pt" span=8 width=64><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; WIDTH: 48pt; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=64>F<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; WIDTH: 48pt; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=64>Current<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; WIDTH: 48pt; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=64>JIM<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; WIDTH: 48pt; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=64 u1:num>
26<o:p></o:p>​


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; WIDTH: 48pt; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=64><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; WIDTH: 48pt; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=64><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; WIDTH: 48pt; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=64>APPS<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; WIDTH: 48pt; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=64 u1:num>
2<o:p></o:p>​


</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">F<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">Current<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">JIM<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
26<o:p></o:p>​


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">SMITH<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
4<o:p></o:p>​


</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">F<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">Current<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">JIM<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
26<o:p></o:p>​


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">APPS<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
6<o:p></o:p>​


</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">F<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">Current<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">JIM<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
26<o:p></o:p>​


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">SMITH<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
8<o:p></o:p>​


</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">F<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">Current<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">JIM<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
26<o:p></o:p>​


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">APPS<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
9<o:p></o:p>​


</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">F<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">Current<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">JIM<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
26<o:p></o:p>​


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">SMITH<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
11.2<o:p></o:p>​


</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">F<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">Current<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">JIM<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
26<o:p></o:p>​


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">APPS<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
13<o:p></o:p>​


</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 7"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">F<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">Current<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">JIM<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
26<o:p></o:p>​


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">SMITH<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
14.8<o:p></o:p>​


</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 8"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">F<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">Current<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">JIM<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
26<o:p></o:p>​


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">APPS<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
16.6<o:p></o:p>​


</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 9"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">F<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">Current<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">JIM<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
26<o:p></o:p>​


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">SMITH<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
18.4<o:p></o:p>​


</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 10"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">F<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">Current<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">JIM<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
26<o:p></o:p>​


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">APPS<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
20.2<o:p></o:p>​


</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 11; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">F<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">Current<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">JIM<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
26<o:p></o:p>​


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm"><o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm">SMITH<o:p></o:p>
​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" u1:num>
22<o:p></o:p>​


</TD></TR></TBODY></TABLE>
<o:p></o:p>
<o:p></o:p>
thanks in advance for any assistance. <o:p></o:p>
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Hi,

Welcome to the forum!

Maybe the new data have leading or trailing spaces that you are not seeing.

Just in case, try this
=SUMPRODUCT(--(TRIM('DATA-INPUT'!$B$2:$B$4899)="Current"),--(TRIM('DATA-INPUT'!$A$2:$A$4899)=$C$4),--(TRIM('DATA-INPUT'!$C$2:$C$4899)=CHANNEL),--(TRIM('DATA-INPUT'!$G$2:$G$4899)=F$4),--(TRIM('DATA-INPUT'!$D$2:$D$4899)=$B7),('DATA-INPUT'!$H$2:$H$4899))

HTH

M.
 
Upvote 0
re:" I separated the formula into its various components and all bar the column "G" resolved to "1". "

What did column G resolve to?
 
Upvote 0
What did column G resolve to?

column G resolved to 1 when the value of F$4 was "APPS" but 0 when then value of F$4 was "SMITH"
 
Upvote 0
If I understand correctly what you want, then I think this can help you:

<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 /><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><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">C4:</td><td style="text-align: center;background-color: #FFFF00;;">F</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">F4:</td><td style="text-align: center;background-color: #FFFF00;;">APPS</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">Name:</td><td style="background-color: #FFFF00;;">Channel</td><td style="background-color: #FFFF00;;">JIM</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;">2</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><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;">3</td><td style="font-weight: bold;text-align: center;;">Col01</td><td style="font-weight: bold;text-align: center;;">Col02</td><td style="font-weight: bold;text-align: center;;">Col03</td><td style="font-weight: bold;text-align: center;;">Col04</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Col05</td><td style="font-weight: bold;text-align: center;;">Col07</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;">4</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">28</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">SMITH</td><td style="text-align: center;;">4.0</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;">5</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">21</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">SMITH</td><td style="text-align: center;;">8.0</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;">6</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">25</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">SMITH</td><td style="text-align: center;;">11.2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFFF00;;">Column B</td><td style="font-weight: bold;text-align: center;;">Result</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">21</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">SMITH</td><td style="text-align: center;;">14.8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">20</td><td style="text-align: center;;">43.8</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">28</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">SMITH</td><td style="text-align: center;;">18.4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">21</td><td style="text-align: center;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">21</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">SMITH</td><td style="text-align: center;;">22.0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">22</td><td style="text-align: center;;">43.6</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">26</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">SMITH</td><td style="text-align: center;;">25.6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">23</td><td style="text-align: center;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">25</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">SMITH</td><td style="text-align: center;;">29.2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">24</td><td style="text-align: center;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">30</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">SMITH</td><td style="text-align: center;;">32.8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">25</td><td style="text-align: center;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">25</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">SMITH</td><td style="text-align: center;;">36.4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">26</td><td style="text-align: center;;">44.0</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">29</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">SMITH</td><td style="text-align: center;;">40.0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">27</td><td style="text-align: center;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">26</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">SMITH</td><td style="text-align: center;;">43.6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">28</td><td style="text-align: center;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">20</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">APPS</td><td style="text-align: center;;">2.0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">29</td><td style="text-align: center;;">64.4</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">29</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">APPS</td><td style="text-align: center;;">6.0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">30</td><td style="text-align: center;;">67.8</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">22</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">APPS</td><td style="text-align: center;;">9.0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">31</td><td style="text-align: center;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">26</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">APPS</td><td style="text-align: center;;">13.0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">32</td><td style="text-align: center;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">30</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">APPS</td><td style="text-align: center;;">16.6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">33</td><td style="text-align: center;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">29</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">APPS</td><td style="text-align: center;;">20.2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">34</td><td style="text-align: center;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">30</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">APPS</td><td style="text-align: center;;">23.8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">35</td><td style="text-align: center;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">30</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">APPS</td><td style="text-align: center;;">27.4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">36</td><td style="text-align: center;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">26</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">APPS</td><td style="text-align: center;;">31.0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">37</td><td style="text-align: center;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">22</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">APPS</td><td style="text-align: center;;">34.6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">38</td><td style="text-align: center;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">29</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">APPS</td><td style="text-align: center;;">38.2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">39</td><td style="text-align: center;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;;">F</td><td style="text-align: center;;">Current</td><td style="text-align: center;;">JIM</td><td style="text-align: center;;">20</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">APPS</td><td style="text-align: center;;">41.8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">40</td><td style="text-align: center;;">0.0</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">Sheet3</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>Worksheet 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">M7</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$B$4:$B$27="Current"</font>),--(<font color="Red">$A$4:$A$27=$C$1</font>),--(<font color="Red">$C$4:$C$27=CHANNEL</font>),--(<font color="Red">$G$4:$G$27=$F$1</font>),--(<font color="Red">$D$4:$D$27=$L7</font>),$H$4:$H$27</font>)</td></tr></tbody></table></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">CHANNEL</th><td style="text-align:left">=Sheet3!$J$1</td></tr></tbody></table></td></tr></table><br />
Markmzz
 
Upvote 0
At present your formula consists of bits such as
('DATA-INPUT'!$A$2:$A$4899=$C$4)
and
('DATA-INPUT'!$C$2:$C$4899=CHANNEL)
and
('DATA-INPUT'!$G$2:$G$4899=F$4)
which you multiply together.

For the Bn range components you need to add them instead:
('DATA-INPUT'!$D$2:$D$4899=$B$7)
and
('DATA-INPUT'!$D$2:$D$4899=$B$8)
and
('DATA-INPUT'!$D$2:$D$4899=$B$9)
etc.

and do so before multiplying the result by using parentheses:
=SUM(('DATA-INPUT'!$B$2:$B$4899="Current")*('DATA-INPUT'!$A$2:$A$4899=$C$4)*('DATA-INPUT'!$C$2:$C$4899=CHANNEL)*('DATA-INPUT'!$G$2:$G$4899=F$4)*
(('DATA-INPUT'!$D$2:$D$4899=$B$7)+('DATA-INPUT'!$D$2:$D$4899=$B$8)+('DATA-INPUT'!$D$2:$D$4899=$B$9))
*('DATA-INPUT'!$H$2:$H$4899))

but include more for B10 to B27.
Clumsy I know, but this works until I/someone thinks of a slicker answer.
One snag is that if there are blanks in B7:B27 it will count blanks in from column D too, so put something in the unused cells of B7:B27 which isn't to be found anywhere in column D, say a "¬" character or something - until, again, I/someone thinks of a slicker answer!
 
Last edited:
Upvote 0
Shortened, try (for all B7:B27):

=SUM(('DATA-INPUT'!$B$2:$B$4899="Current")*('DATA-INPUT'!$A$2:$A$4899=$C$4)*('DATA-INPUT'!$C$2:$C$4899=CHANNEL)*('DATA-INPUT'!$G$2:$G$4899=F$4)*
(ISNUMBER(MATCH('DATA-INPUT'!$D$2:$D$4899,$B$7:$B$27,0)))
*('DATA-INPUT'!$H$2:$H$4899))

Sumproduct instead of sum, prevents needing to Array-Enter:

=SUMPRODUCT(('DATA-INPUT'!$B$2:$B$4899="Current")*('DATA-INPUT'!$A$2:$A$4899=$C$4)*('DATA-INPUT'!$C$2:$C$4899=CHANNEL)*('DATA-INPUT'!$G$2:$G$4899=F$4)*
(ISNUMBER(MATCH('DATA-INPUT'!$D$2:$D$4899,$B$7:$B$27,0)))
*('DATA-INPUT'!$H$2:$H$4899))
 
Upvote 0
Hi P45Cal and Markmizz,

first off. Apologies my bad !! Poor communications skills on my behalf.

The $Bn column values are text values ie there will be values like K1, G3 as well as the 26 I entered in my example.

So that basically there are 5 conditions that select the cells to be summed from the range $H2:$H$4899

To my mind the formula works like a pivot table in that only when ALL the conditions are met will the numbers be summed for that particular group of values.

Why isn't it just a pivot table with some VBA to take the summed values and put them into the target areas of the target sheets ???

Answer : That is the way it was handed over ! I am starting to form the opinion that only if I make a pivot table of the data in 'DATA-INPUT' sheet will I be able to sum the data for each group of
('DATA-INPUT'!$B$2:$B$4899="Current")* "Standard or Current widget"
('DATA-INPUT'!$A$2:$A$4899=$C$4)* "Company Source"
('DATA-INPUT'!$C$2:$C$4899=CHANNEL)* "Comms method - thru_door, Tel1, Tel2, Internet"
('DATA-INPUT'!$G$2:$G$4899=F$4)* "Process Stage app, acc"
('DATA-INPUT'!$D$2:$D$4899=$B7)* "Customer type 01,08,26, K1, G3"
('DATA-INPUT'!$H$2:$H$4899)) "The actual numbers to be summed."

thanks for your efforts,

MIsim
 
Last edited:
Upvote 0
Did you try it? Don't be fooled by the ISNUMBER component - it works with strings too.
 
Upvote 0
Hi P45Cal,

first off. Apologies my bad !! Poor communications skills on my behalf.

The $Bn column values are text values ie there will be values like K1, G3 as well as the 26 I entered in my example.

So that basically the 5 conditions select the cells to be summed from the range $H2:$H$4899

To my mind the formula works like a pivot table in that only when ALL the conditions are met will the numbers be summed for that particular group of values.

Why isn't it just a pivot table with some VBA to take the summed values and put them into the target areas of the target sheets ??? Answer : That is the way it was handed over ! I am starting to form the opinion that only if I make a pivot table of the data in 'DATA-INPUT' sheet will I be able to sum the data for each group of
('DATA-INPUT'!$B$2:$B$4899="Current")* Standard or Current widget
('DATA-INPUT'!$A$2:$A$4899=$C$4)* Company Source
('DATA-INPUT'!$C$2:$C$4899=CHANNEL)* Comms method - thru_door, Tel1, Tel2, Internet
('DATA-INPUT'!$G$2:$G$4899=F$4)* Process Stage app, acc
('DATA-INPUT'!$D$2:$D$4899=$B7)* Customer type 01,08,26, K1, G3
('DATA-INPUT'!$H$2:$H$4899)) The actual numbers to be summed.

thanks for your efforts,

MIsim
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
619
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
Adding a custom message.
Replies
1
Views
660
Micron
M
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E

Forum statistics

Threads
1,224,548
Messages
6,179,448
Members
452,915
Latest member
hannnahheileen

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