# Thread: SumIF and SumProduct (2000)

1. ## 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. ## Re: SumIF and SumProduct (2000)

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

#### Posting Permissions

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