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

    SumIF and SumProduct (2000)

    In the following table the fomula in B2 is =SumIf(Years,A2,MyData). The result is only totaling the first column of MyData rather than all three columns which is what I want.
    I also tried =SumProduct(Years=A2)*(MyData) which returned a #Value error -- (I think because the arrays are different sizes)
    and
    =SumProduct(Year=A2)*($D$11:$D$21) which returned zero

    How can I get the total of all 3 columns in MyData if the Year match, say A2?



    <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)

    Hi Paul,
    Try:
    <code>=SumProduct((Years=A2)*MyData)</code>
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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