SumIF help

[singingsister]

Hi

I have a spreadsheet showing telecoms usage split over different sites. The sheet looks something like this

Site number telephone number jan feb mar
123456 0123 456 78 usage
line rental
broadband
other


0123 456 79 usage
line rental
broadband
other
LINE FOR SITE TOTALS
234567 01578 455 55 usage
line rental
broadband
other
01578 455 56 usage
line rental
broadband
other
LINE FOR SITE TOTALS

There are obviously figures for each of the criteria in each of the months. What I now need to do is have 4 totals at the bottom for total usage, line rental, broadband and other. I tried using sumif, but I don't want to include the rows that have the site totals in (the line underneath all the telephone numbers of one site)

I keep getting a 0 answer when I use sumif when there should be figures.

How can I get the answer I want?

Sarah

 

[singingsister]

anyone?

 

[Andrew Poulsom]

Can you post a clearer sample of your data please? I don't see any numbers in your current sample.

If you select 'Enhanced Interface - Full WYSIWYG Editing' in 'Message Editor Interface' under User CP|Edit Options (menu towards top of screen on Board) you can copy it in Excel and paste it into a reply. It's best to surround each cell in the range with borders. Please give cell references.

 

[Peter_SSs]

What columns are those various things in?

What SUMIF formula did you try?

 

[MikeWx]

I am guessing your data looks similar to the following:

Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 85px"><COL style="WIDTH: 130px"><COL style="WIDTH: 74px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Site Number</TD><TD>Telephone Number</TD><TD>Usage</TD><TD>Jan</TD><TD>Feb</TD><TD>Mar</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">123456</TD><TD>0123 456 78</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD></TD><TD></TD><TD>line rental</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">9</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD></TD><TD></TD><TD>broadband</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">6</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD></TD><TD></TD><TD>other</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD></TD><TD>0123 456 79</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD></TD><TD></TD><TD>line rental</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right">18</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD></TD><TD></TD><TD>broadband</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">9</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD></TD><TD></TD><TD>other</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>SITE TOTALS</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">29</TD><TD style="TEXT-ALIGN: right">36</TD><TD style="TEXT-ALIGN: right">44</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">234567</TD><TD>01578 455 55</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD></TD><TD></TD><TD>line rental</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">17</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD></TD><TD></TD><TD>broadband</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">8</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD></TD><TD></TD><TD>other</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD></TD><TD>01578 455 56</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD></TD><TD></TD><TD>line rental</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">15</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD></TD><TD></TD><TD>broadband</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">7</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD></TD><TD></TD><TD>other</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD>SITE TOTALS</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">38</TD><TD style="TEXT-ALIGN: right">38</TD><TD style="TEXT-ALIGN: right">49</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD></TD><TD></TD><TD>total usage</TD><TD style="TEXT-ALIGN: right">67</TD><TD style="TEXT-ALIGN: right">74</TD><TD style="TEXT-ALIGN: right">93</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD></TD><TD></TD><TD>line rental</TD><TD style="TEXT-ALIGN: right">36</TD><TD style="TEXT-ALIGN: right">50</TD><TD style="TEXT-ALIGN: right">59</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD></TD><TD></TD><TD>broadband</TD><TD style="TEXT-ALIGN: right">22</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">30</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD></TD><TD></TD><TD>other</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">4</TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

If it does, the formula for cell D22, which you can copy to D23 and D24 is

=SUMIF($C$2:$C$19,C2&">' '",D$2:D$19)

The formula for cell D23, which can be copied to the remaining totals in row 23 through 25 is

=SUMIF($C$2:$C$19,$C23,D$2:D$19)

Mike

 

[Peter_SSs]

.. the formula for cell D22, which you can copy to D23 and D24 is

=SUMIF($C$2:$C$19,C2&">' '",D$2:D$19)

Mike, two things.

1. For the red bit, did you mean "copy to E22 and F22"?

2. Since we are told there is to be four totals at the bottom, and your SUMIFs do the other three, then D22 (copied across) could simply be
=SUM(D23:D25)