SUMIFS using multiple validation list filters

[AusSteelMan]

Hi everyone,

I have the following "Summary" sheet - the start of a basic dashboard i suppose you could say.

Excel Workbook
	D	E	F	G	H	I	J	K	L
9	Section	Idea Category	Idea type	Status				Planned Saving	Savings to Date
10	Distribution	SV28 - Shut Down Capital (above) 500K	Cost Saving	Started			July 2012	$ 50,000	$ 52,900
11							August 2012	$ -	$ -
12							September 2012	$ -	$ -
Summary

The data is sourced from sheet "SavingsIdeas"

The criteria in cells D10:G10 are all selected from validation lists (on both sheets)

For the example above, the results in K10 and L10 are correct - so I am happy that the formulae work OK

What I would like to be able to do though, is not have all 4 criteria selected. For example, I would like to leave "Section" (D10) blank and have the results return the totals for all sections (7 in all at the moment), but still use the other criteria. I would like to do this for all the criteria, to a point where if no criteria was selected, then the total for all 4 criteria would be summed and displayed.

Hope this makes sense.

I figure I could probably pull it off with a complex series of other IF statements, but thought i'd check if there was an simpler method.

Many thanks for reading and any suggestions you can provide.

Have a great day,
Darren

 

[Jerry Sullivan]

Hi Darren,

Here's one way...

=SUMIFS(SavingsIdeas!$G:$G,SavingsIdeas!$C:$C,IF(ISBLANK($J10),">0",J10),
SavingsIdeas!$B:$B,IF(ISBLANK(D10),"*",D10),SavingsIdeas!$E:$E,IF(ISBLANK(E10),"*",E10),
SavingsIdeas!$F:$F,IF(ISBLANK(F10),"*",F10),SavingsIdeas!$J:$J,IF(ISBLANK(G10),"*",G10))

 

[AusSteelMan]

Thanks Jerry. Works great of course.

I thought something like that with the IF function may be required. I knew I needed a method to choose any value (incl blank), but thanks for reminding me that I needed the "*" in there. I had forgotten about making it a wildcard.

Best regards,
Darren