SUMIFS - multiple criteria (one dynamic) across several columns of data

Chelseablue

New Member
Joined
May 9, 2016
Messages
5
Hi,

I am trying to create a SUMIFS formula to calculate predicted sales of widgets by salesman, by time period and by widget type. The results are then displayed in a summary table. The formula works fine for a single column but when expanded to include the full range of widgets - it falls over.

I want the Widget type to be selected from a drop down list in cell A1 and as the type is changed the summary table will display the results.

The formula I am trying to use is: =SUMIFS(C3:F12,C2:F2,A1,A3:A12,B20,B3:B12,A21) but returns a #VALUE! error

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Widget A</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: center;;">Widget A</td><td style="text-align: center;;">         Widget B</td><td style="text-align: center;;">         Widget C</td><td style="text-align: center;;">Widget D</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Fred</td><td style=";">2016  Q1</td><td style="text-align: right;;">£5,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">George</td><td style=";">2016  Q1</td><td style="text-align: right;;">£1,000.00</td><td style="text-align: right;;">£2,000.00</td><td style="text-align: right;;">£500.00</td><td style="text-align: right;;">£500.00</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Fred</td><td style=";">2016  Q3 </td><td style="text-align: right;;">£500.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Fred</td><td style=";">2016  Q4 </td><td style="text-align: right;;">£250.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Fred</td><td style=";">2017 Q3 </td><td style="text-align: right;;">£1,000.00</td><td style="text-align: right;;">£1,000.00</td><td style="text-align: right;;">£750.00</td><td style="text-align: right;;">£250.00</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">George</td><td style=";">2017  Q4</td><td style="text-align: right;;">£1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Andrew</td><td style=";">2016  Q4 </td><td style=";">  </td><td style="text-align: right;;">£2,000.00</td><td style="text-align: right;;">£500.00</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Andrew</td><td style=";">2016  Q2 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Andrew</td><td style=";">2016  Q3 </td><td style="text-align: right;;">£1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Andrew</td><td style=";">2016  Q2</td><td style="text-align: right;;"></td><td style="text-align: right;;">£5,000.00</td><td style="text-align: right;;">£5,000.00</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;"></td><td style=";">Andrew</td><td style=";">Fred</td><td style=";">George</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">2016  Q1</td><td style=";">#VALUE!</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">2016  Q2</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">2016  Q3</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">2016  Q4</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style=";">2017  Q1</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style=";">2017  Q2</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style=";">2017 Q3+</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

I have been looking at this formula for several days now and must confess to going around in circles - any help that you can provide would very gratefully received.

Hopefully I have given sufficient information.

regards
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try

=SUMIFS(INDEX(C3:F12,0,MATCH(A1,C2:F2,0)),A3:A12,B20,B3:B12,A21)
 

Chelseablue

New Member
Joined
May 9, 2016
Messages
5
Fantastic !

That has worked a treat, thank you so much for the help and saving my sanity :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,387
Messages
5,636,010
Members
416,892
Latest member
Bensch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top