# Thread: SumIF and SumProduct (2000)

1. ## 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. ## 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

3. ## 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
•