Drop-down list exception

morrisco

New Member
Joined
Aug 5, 2011
Messages
3
I posted a thread earlier about a balance sheet and now I have another question.

I have an expenses table that dispalys all the expenses that happened during a given year, the year is displayed on the formula as a reference to a cell that contains a drop down list where you pick the year. So if I pick 2010 on this drop down list the expenses table displays all the expenses of that happened during 2010, if I pick 2011 it dispalys the transactions of 2011, and so on.

So what I wanted to know if there is some sort of command that I can add to the data source of the drop down list that works as a "all of the above" option. So that if I pick that all of the above option the table will display all the expenses. Is this possible or what is the closest thing to it?

I think it is important to clarify that the expenses are chosen from a transactions table using a SUMIFS formula that checks if the transactions belong to the year chosen in the drop down list. So what I need to have is an option in the drop down list that makes the SUMIFS formula choose transactions from any year.

Can this be done?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Hamburgler

Board Regular
Joined
May 16, 2009
Messages
148
You can use expressions like ">" in SUMIFS etc formulas, for example you can do:

Code:
=SUMIF(CriteriaRange, ">=" & 2005, SumRange)

to sum Values for year >= 2005.

Also, if you are currently using VALIDATION for you dropdown list you may also want to check out he LISTBOX and COMBOBOX controls available in the DEVELOPER tab.
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,677
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Maybe something like

Include (all) as the last element of your validation list and reset the validation range to include this.

You validation-list wiil look like

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>List</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>2002</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>2003</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>2004</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>(all)</TD></TR></TBODY></TABLE>

Assuming E2 as the validated-cell, change your formula to something like

=SUMIF(A2:A10,IF(E2="(all)",">0",E2),C2:C10)

where A2:A10 (numbers) is the criteria-range and C2:C10 the range to be summed.

If A2:A10 has text-values you can change the formula to
=SUMIF(A2:A10,IF(E2="(all)","*",E2),C2:C10)

HTH

M.
 

Forum statistics

Threads
1,136,971
Messages
5,678,897
Members
419,788
Latest member
Mukund2903

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