How do I sum values based upon another field setting?

[WildAngus]

Col B Col C
Row 2 Count? Value
Row 3 Y 10
Row 4 N 20
Row 5 Y 30
Row 6 N 40
Row 7 Y 50
Row 8
Row 9

What formula will total all of Col C range (Row 3 to 7) where Col B is a "Y"? (i.e. result should be 90). Tried a few things and can't get it to work. Can anyone suggest a solution please?

(This doesn't format very well, but I guess you can see where I'm coing from)

 

[crook_101]

Hi,

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Y</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">90</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD>N</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD>Y</TD><TD style="TEXT-ALIGN: right">30</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD>n</TD><TD style="TEXT-ALIGN: right">40</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>y</TD><TD style="TEXT-ALIGN: right">50</TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Sheet2​

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>D1</TH><TD style="TEXT-ALIGN: left">=SUMIF(B1:B5,B1,C1:C5)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Expand the range(s) to suit your data


Regards,
Ian

 

[Dannyh1]

Hi try,

=SUMIF(B:B,"Y",C:C)

 

[WildAngus]

Cheers guys - Both work a treat.

Thanks a lot