Suming a column based on criteria in another column

[Shamimo]

Hello,
I have a table (named "ALL" ) with a lot of columns and i need to sum the figures in column B (named : Jul-11) only if the corresponding line in column A (named Status) equals to "S".
The data are not in a predefined range and are formatted in a table.
i tried this formula but it returns #value even if all data in column B are numbers.
<table style="width: 80px; height: 20px;" border="0" cellpadding="0" cellspacing="0"><tr height="20"><td class="xl67" style="height:15.0pt; width:60pt" align="center" height="20" width="80">
</td></tr></table>=SUM(IF(ALL[Status]="S",ALL[Jul-11]))

Can someone help on this please ?

Thanks a lot

 

[Matty]

Welcome to the Board...

Does this return the desired result?

=SUMIF(A2:A100,"S",B2:B100)

Change ranges to suit.

Matty

 

[Shamimo]

Matty ... my only words to you are Thank You!!!

It worked perfectly... i tried it before in another sheet but it didnt seem to work at that time. I must have done something wrong then...

Thanks again!

 

[Matty]

Matty ... my only words to you are Thank You!!!

It worked perfectly... i tried it before in another sheet but it didnt seem to work at that time. I must have done something wrong then...

Thanks again!

You're very welcome. Happy to help.

That formula should evaluate correctly even if there is a non-numeric value in Column B while Column A contains "S".

Matty

 

[markmzz]

Hello,
I have a table (named "ALL" ) with a lot of columns and i need to sum the figures in column B (named : Jul-11) only if the corresponding line in column A (named Status) equals to "S".
The data are not in a predefined range and are formatted in a table.
i tried this formula but it returns #value even if all data in column B are numbers.
<TABLE style="WIDTH: 80px; HEIGHT: 20px" border=0 cellSpacing=0 cellPadding=0><TBODY><TR height=20><TD style="WIDTH: 60pt; HEIGHT: 15pt" class=xl67 height=20 width=80 align=center>




</TD></TR></TBODY></TABLE>=SUM(IF(ALL[Status]="S",ALL[Jul-11]))

Can someone help on this please ?

Thanks a lot

Or try this array formula (use Ctrl+Shift+Enter and not only Enter):

Note: You forgot the Ctrl+Shift+Enter.

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center">Status</TD><TD>jul/11</TD><TD style="TEXT-ALIGN: right"></TD><TD>Total S</TD><TD style="TEXT-ALIGN: right">2229</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: right">195</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">C</TD><TD style="TEXT-ALIGN: right">579</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center">S</TD><TD style="TEXT-ALIGN: right">238</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">S</TD><TD style="TEXT-ALIGN: right">955</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center">C</TD><TD style="TEXT-ALIGN: right">464</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: right">921</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center">S</TD><TD style="TEXT-ALIGN: right">455</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: center">A</TD><TD style="TEXT-ALIGN: right">842</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: right">444</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: right">364</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: center">C</TD><TD style="TEXT-ALIGN: right">784</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: center">A</TD><TD style="TEXT-ALIGN: right">128</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: center">C</TD><TD style="TEXT-ALIGN: right">587</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: right">937</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: right">698</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: center">S</TD><TD style="TEXT-ALIGN: right">581</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="TEXT-ALIGN: center">C</TD><TD style="TEXT-ALIGN: right">401</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="TEXT-ALIGN: center">C</TD><TD style="TEXT-ALIGN: right">958</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: right">381</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD style="TEXT-ALIGN: center"></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="TEXT-ALIGN: center; COLOR: #161120">22</TD><TD style="TEXT-ALIGN: center">**********</TD><TD>**********</TD><TD>**********</TD><TD>**********</TD><TD>**********</TD></TR></TBODY></TABLE>



<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E1</TH><TD style="TEXT-ALIGN: left">{=SUM(IF(ALL[Status]="S",ALL[jul/11]))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself


</TD></TR></TBODY></TABLE>


Markmzz

 

[Shamimo]

Thanks a lot! i will try this solution too.. cheers