Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SumIF and SumProduct (2000)

    How do I get B2 to return the sum of all 3 columns (instead of just the first column) in MyData if the Year is, say A2

    I also tried =SumProduct(Years=A2)*(MyData) This returned a #Value error
    and = SumProduct(Years=A2)*($D$11:$D$21) which returned zero

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>F</td><td align=center valign=bottom>1</td><td align=right valign=bottom></td><td valign=bottom>Total per Yr</td><td align=right valign=bottom></td><td valign=bottom>Formula</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>2</td><td align=right valign=bottom>2000</td><td align=right valign=bottom>28</td><td align=right valign=bottom></td><td valign=bottom>SUMIF(Years,A2,MyData)</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>3</td><td align=right valign=bottom>2001</td><td align=right valign=bottom>32</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>4</td><td align=right valign=bottom>2002</td><td align=right valign=bottom>0</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>5</td><td align=right valign=bottom>2003</td><td align=right valign=bottom>30</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>6</td><td align=right valign=bottom>2004</td><td align=right valign=bottom>5</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>7</td><td align=right valign=bottom>2005</td><td align=right valign=bottom>9</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>8</td><td align=right valign=bottom>2006</td><td align=right valign=bottom>16</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>9</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>10</td><td valign=bottom>Years</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td valign=bottom>MyData</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>11</td><td align=right valign=bottom>2005</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom>5</td><td align=right valign=bottom>7</td><td align=right valign=bottom>2</td><td align=center valign=bottom>12</td><td align=right valign=bottom>2001</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom>10</td><td align=right valign=bottom>8</td><td align=right valign=bottom>5</td><td align=center valign=bottom>13</td><td align=right valign=bottom>2006</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom>15</td><td align=right valign=bottom>9</td><td align=right valign=bottom>8</td><td align=center valign=bottom>14</td><td align=right valign=bottom>2001</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom>20</td><td align=right valign=bottom>4</td><td align=right valign=bottom>1</td><td align=center valign=bottom>15</td><td align=right valign=bottom>2000</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom>25</td><td align=right valign=bottom>5</td><td align=right valign=bottom>4</td><td align=center valign=bottom>16</td><td align=right valign=bottom>2003</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom>30</td><td align=right valign=bottom>6</td><td align=right valign=bottom>7</td><td align=center valign=bottom>17</td><td align=right valign=bottom>2006</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom>1</td><td align=right valign=bottom>1</td><td align=right valign=bottom>3</td><td align=center valign=bottom>18</td><td align=right valign=bottom>2001</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom>2</td><td align=right valign=bottom>2</td><td align=right valign=bottom>6</td><td align=center valign=bottom>19</td><td align=right valign=bottom>2000</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom>3</td><td align=right valign=bottom>3</td><td align=right valign=bottom>9</td><td align=center valign=bottom>20</td><td align=right valign=bottom>2005</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom>4</td><td align=right valign=bottom>2</td><td align=right valign=bottom>3</td><td align=center valign=bottom>21</td><td align=right valign=bottom>2004</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom>5</td><td align=right valign=bottom>5</td><td align=right valign=bottom>5</td></table>

    Thanks

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: SumIF and SumProduct (2000)

    Isn't this the same question you just posted? (So I'll give you the same answer! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
    =SUMPRODUCT((Years=A2)*MyData)

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIF and SumProduct (2000)

    Rory
    Sorry for the double post. The first one returned a Crash msg: Sorry the Lounge is overloaded....Try Later.
    So, I tried later <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •