Sumproduct with multiple criteria

[rwood]

I need help with the following formula. {=SUMPRODUCT(--Status="A")*--(LEFT($C$18:$C$1253,6)="NC1101")}
The answer I'm looking for is "1" but the formula is returning "0".
Spreadsheet
A B C
<table border="0" cellpadding="0" cellspacing="0" width="202"><col style="width: 41pt;" width="54"> <col style="width: 48pt;" width="64"> <col style="width: 63pt;" width="84"> <tbody><tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; width: 41pt; font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(0, 176, 80);" height="19" width="54">A</td> <td class="xl67" style="width: 48pt; font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; border: 0.5pt solid windowtext;" width="64">PF</td> <td class="xl67" style="width: 63pt; font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; border: 0.5pt solid windowtext;" width="84">NC110117</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(0, 176, 80);" height="19">A</td> <td class="xl67" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; border: 0.5pt solid windowtext;">PF</td> <td class="xl67" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; border: 0.5pt solid windowtext;">NC101212</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(0, 176, 80);" height="19">A</td> <td class="xl67" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; border: 0.5pt solid windowtext;">PF</td> <td class="xl67" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; border: 0.5pt solid windowtext;">NC101003</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(0, 176, 80);" height="19">A</td> <td class="xl67" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; border: 0.5pt solid windowtext;">RW</td> <td class="xl67" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; border: 0.5pt solid windowtext;">NC100904</td> </tr> <tr style="height: 18pt;" height="24"> <td class="xl67" style="height: 18pt; font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(0, 176, 80);" height="24">A</td> <td class="xl67" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; border: 0.5pt solid windowtext;">DM</td> <td class="xl67" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; border: 0.5pt solid windowtext;">NC101108</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(0, 112, 192);" height="19">B</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">PF</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">NC100628</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(0, 112, 192);" height="19">B</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">PF</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">NC100718</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl67" style="height: 13.5pt; font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(0, 112, 192);" height="18">B</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">PF</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">NC100811</td> </tr> </tbody></table>
I want to count the number if proposals (Col "C") that have been awarded (COL A) per month. The search for the proposal # is the first (6) characters. NC110101 = NC (State) 11= "2011" 01="Month" 01= (# proposal for the month).

 

[barry houdini]

How is Status defined - does it start at A18? Try

=SUMPRODUCT(--($A$18:$A$1253="A"),--(LEFT($C$18:$C$1253,6)="NC1101"))

It doesn't need array entering.....

 

[Aladin Akyurek]

I need help with the following formula. {=SUMPRODUCT(--Status="A")*--(LEFT($C$18:$C$1253,6)="NC1101")}
The answer I'm looking for is "1" but the formula is returning "0".
Spreadsheet
A B C
<TABLE border=0 cellSpacing=0 cellPadding=0 width=202><COLGROUP><COL style="WIDTH: 41pt" width=54><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 63pt" width=84><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 41pt; FONT-FAMILY: Arial; BACKGROUND: rgb(0,176,80); HEIGHT: 14.25pt; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none" class=xl67 height=19 width=54>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; FONT-FAMILY: Arial; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none" class=xl67 width=64>PF</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 63pt; FONT-FAMILY: Arial; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none" class=xl67 width=84>NC110117</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: rgb(0,176,80); HEIGHT: 14.25pt; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none" class=xl67 height=19>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none" class=xl67>PF</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none" class=xl67>NC101212</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: rgb(0,176,80); HEIGHT: 14.25pt; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none" class=xl67 height=19>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none" class=xl67>PF</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none" class=xl67>NC101003</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: rgb(0,176,80); HEIGHT: 14.25pt; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none" class=xl67 height=19>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none" class=xl67>RW</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none" class=xl67>NC100904</TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: rgb(0,176,80); HEIGHT: 18pt; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none" class=xl67 height=24>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none" class=xl67>DM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none" class=xl67>NC101108</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: rgb(0,112,192); HEIGHT: 14.25pt; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none" class=xl67 height=19>B</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl67>PF</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl67>NC100628</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: rgb(0,112,192); HEIGHT: 14.25pt; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none" class=xl67 height=19>B</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl67>PF</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl67>NC100718</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: rgb(0,112,192); HEIGHT: 13.5pt; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none" class=xl67 height=18>B</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl67>PF</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl67>NC100811</TD></TR></TBODY></TABLE>
I want to count the number if proposals (Col "C") that have been awarded (COL A) per month. The search for the proposal # is the first (6) characters. NC110101 = NC (State) 11= "2011" 01="Month" 01= (# proposal for the month).

Try rather...

=SUMPRODUCT(--(Status="A"),--(LEFT($C1C$18:$C$1253,6)="NC1101"))

Status must have the same size as C18:C1253 like in $A$18:$A$1253.

 

[rwood]

Thanks Barry. This formula works.