sumIF(AND? - off a drop down menu

P

Pizzio

Active Member
Joined
Apr 28, 2005
Messages
275
I need to sum expense data by three components types using a drop down menu.

I need a formula that will do the following:

If the expense type drop down (cell A1) = ALL then sum all expense types, IF NOT then sum only the expense type stated.

AND

If the location type drop down (cell b1) = ALL then sum all the location types, IF NOT then sum only the location types stated.

AND

If the date is = to cell c1

Any ideas?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Pizzio said:
I need to sum expense data by three components types using a drop down menu.

I need a formula that will do the following:

If the expense type drop down (cell A1) = ALL then sum all expense types, IF NOT then sum only the expense type stated.

AND

If the location type drop down (cell b1) = ALL then sum all the location types, IF NOT then sum only the location types stated.

AND

If the date is = to cell c1

Any ideas?
Click to expand...

Control+shift+enter, not just enter:
Code: 
=SUM(
    IF(RangeExpenseTypes=IF(A1="ALL",RangeExpenseTypes,A1),
    IF(RangeLocationTypes=IF(B1="ALL",RangeLocationTypes,B1),
    IF(DateRange=C1,
      SumRange))))
 
Upvote 0
Try this (array formula - use Ctrl+Shift+Enter and not only Enter):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;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><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">All</td><td style="text-align: center;background-color: #92D050;;">TypeLocal03</td><td style="text-align: center;background-color: #FFFF00;;">2/14/2011</td><td style="text-align: center;background-color: #C5D9F1;;">Result</td><td style="font-weight: bold;text-align: center;;">Exp</td><td style="font-weight: bold;text-align: center;;">Local</td><td style="font-weight: bold;text-align: center;;">Date</td><td style="font-weight: bold;text-align: center;;">Expense </td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #C5D9F1;;">2074</td><td style="text-align: center;;">TypeExp02</td><td style="text-align: center;background-color: #92D050;;">TypeLocal03</td><td style="text-align: center;;">11/10/2010</td><td style="text-align: center;;">106</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">TypeExp02</td><td style="text-align: center;;">TypeLocal01</td><td style="text-align: center;background-color: #FFFF00;;">2/14/2011</td><td style="text-align: center;;">861</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">TypeExp02</td><td style="text-align: center;background-color: #92D050;;">TypeLocal03</td><td style="text-align: center;;">4/16/2010</td><td style="text-align: center;;">229</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">TypeExp02</td><td style="text-align: center;;">TypeLocal01</td><td style="text-align: center;;">1/3/2012</td><td style="text-align: center;;">327</td></tr><tr ><td style="color: #161120;text-align: center;">6</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><td style="text-align: center;;">TypeExp03</td><td style="text-align: center;;">TypeLocal02</td><td style="text-align: center;;">8/13/2011</td><td style="text-align: center;;">274</td></tr><tr ><td style="color: #161120;text-align: center;">7</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><td style="text-align: center;;">TypeExp02</td><td style="text-align: center;background-color: #92D050;;">TypeLocal03</td><td style="text-align: center;;">2/4/2010</td><td style="text-align: center;;">748</td></tr><tr ><td style="color: #161120;text-align: center;">8</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><td style="text-align: center;;">TypeExp01</td><td style="text-align: center;background-color: #92D050;;">TypeLocal03</td><td style="text-align: center;;">7/27/2009</td><td style="text-align: center;;">181</td></tr><tr ><td style="color: #161120;text-align: center;">9</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><td style="text-align: center;;">TypeExp01</td><td style="text-align: center;background-color: #92D050;;">TypeLocal03</td><td style="text-align: center;background-color: #FFFF00;;">2/14/2011</td><td style="text-align: center;background-color: #C5D9F1;;">812</td></tr><tr ><td style="color: #161120;text-align: center;">10</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><td style="text-align: center;;">TypeExp02</td><td style="text-align: center;;">TypeLocal01</td><td style="text-align: center;;">4/1/2010</td><td style="text-align: center;;">249</td></tr><tr ><td style="color: #161120;text-align: center;">11</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><td style="text-align: center;;">TypeExp03</td><td style="text-align: center;;">TypeLocal02</td><td style="text-align: center;;">9/5/2010</td><td style="text-align: center;;">854</td></tr><tr ><td style="color: #161120;text-align: center;">12</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><td style="text-align: center;;">TypeExp03</td><td style="text-align: center;background-color: #92D050;;">TypeLocal03</td><td style="text-align: center;;">10/15/2011</td><td style="text-align: center;;">389</td></tr><tr ><td style="color: #161120;text-align: center;">13</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><td style="text-align: center;;">TypeExp03</td><td style="text-align: center;background-color: #92D050;;">TypeLocal03</td><td style="text-align: center;;">1/4/2011</td><td style="text-align: center;;">546</td></tr><tr ><td style="color: #161120;text-align: center;">14</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><td style="text-align: center;;">TypeExp01</td><td style="text-align: center;;">TypeLocal01</td><td style="text-align: center;;">11/9/2011</td><td style="text-align: center;;">489</td></tr><tr ><td style="color: #161120;text-align: center;">15</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><td style="text-align: center;;">TypeExp02</td><td style="text-align: center;background-color: #92D050;;">TypeLocal03</td><td style="text-align: center;;">2/4/2011</td><td style="text-align: center;;">186</td></tr><tr ><td style="color: #161120;text-align: center;">16</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><td style="text-align: center;;">TypeExp01</td><td style="text-align: center;background-color: #92D050;;">TypeLocal03</td><td style="text-align: center;background-color: #FFFF00;;">2/14/2011</td><td style="text-align: center;background-color: #C5D9F1;;">842</td></tr><tr ><td style="color: #161120;text-align: center;">17</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><td style="text-align: center;;">TypeExp03</td><td style="text-align: center;background-color: #92D050;;">TypeLocal03</td><td style="text-align: center;;">11/19/2009</td><td style="text-align: center;;">810</td></tr><tr ><td style="color: #161120;text-align: center;">18</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><td style="text-align: center;;">TypeExp02</td><td style="text-align: center;;">TypeLocal01</td><td style="text-align: center;;">7/6/2011</td><td style="text-align: center;;">683</td></tr><tr ><td style="color: #161120;text-align: center;">19</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><td style="text-align: center;;">TypeExp01</td><td style="text-align: center;background-color: #92D050;;">TypeLocal03</td><td style="text-align: center;background-color: #FFFF00;;">2/14/2011</td><td style="text-align: center;background-color: #C5D9F1;;">420</td></tr><tr ><td style="color: #161120;text-align: center;">20</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><td style="text-align: center;;">TypeExp02</td><td style="text-align: center;;">TypeLocal02</td><td style="text-align: center;;">10/23/2011</td><td style="text-align: center;;">898</td></tr><tr ><td style="color: #161120;text-align: center;">21</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><td style="text-align: center;;">TypeExp02</td><td style="text-align: center;;">TypeLocal02</td><td style="text-align: center;;">1/9/2012</td><td style="text-align: center;;">699</td></tr><tr ><td style="color: #161120;text-align: center;">22</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><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">**********</td><td style=";">**********</td><td style=";">**********</td><td style=";">**********</td><td style=";">**********</td><td style=";">**********</td><td style=";">**********</td><td style=";">**********</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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet6</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">A1="All",1,E2:E21=A1</font>)*IF(<font color="Red">B1="All",1,F2:F21=B1</font>)*(<font color="Red">G2:G21=C1</font>)*H2:H21</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz
 
Last edited:
Upvote 0
Thanks guys, BOTH examples worked. I really appreciate the assistance. I was about to tell everyone to go in and do it manually themselves.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Subtotals within Sumifs range
Replies
6
Views
990
jaz44
J
N
  • Question Question
EXCEL VBA COMBO BOX/DROP DOWN LIST
Replies
1
Views
160
Peter_SSs
Peter_SSs
D
  • Question Question
VBA Download URL and rename file
Replies
6
Views
358
Jeffrey Mahoney
Jeffrey Mahoney
J
  • Question Question
Auto-filling multiple drop-down menus based on selection of one drop-down
Replies
5
Views
190
Micron
M
S
  • Question Question
Copy and Paste from Different Sheet base upon Drop Down Criteria
Replies
3
Views
229
Joe4
Joe4

Forum statistics

Threads
1,224,521
Messages
6,179,291
Members
452,902
Latest member
Knuddeluff

Share this page

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
Back
Top