SUMIFS Wildcard

[powelly]

Hi Guys,

I'm using Excel 2016 and trying to retrieve some values from a table, here's a simple example;

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Dept[/TD]
[TD="width: 64"]Sub Dept[/TD]
[TD="width: 64"]Sales[/TD]
[/TR]
[TR]
[TD]Dept A[/TD]
[TD]Dept A1[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Dept A[/TD]
[TD]Dept A2[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD]Dept B[/TD]
[TD]Dept B1[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Dept B[/TD]
[TD]Dept B2[/TD]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD]Dept B[/TD]
[TD]Dept B3[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Inputs[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dept[/TD]
[TD]Dept A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sub Dept[/TD]
[TD]Dept A1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Output[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Where the formula in the output is "=SUMIFS( G8:G12, E8:E12, F15, F8:F12, F16 )"

What I'm looking for is the ability to sum up either all Depts or Sub Depts, I thought I could use wildcards in the inp[ut cells for this but it doesn't seem to work. Can anyone help?

 

[powelly]

Edit: formula is "=SUMIFS( C2:C6, A2:A6, B9, B2:B6, B10 )"

 

[Marcelo Branco]

Edit: formula is "=SUMIFS( C2:C6, A2:A6, B9, B2:B6, B10 )"

Your formula works if you insert an asterisk (*) in either B9 or B10 or in both.

M.

 

[powelly]

Your formula works if you insert an asterisk (*) in either B9 or B10 or in both.

M.

Right. But I want to keep the formula fixed and then input either wildcards (for all depts) or a department name (for a single dept). Obviously my actual data and output criteria is much larger than this example.

 

[powelly]

actually NM, I am a moron.

 

[Marcelo Branco]

Right. But I want to keep the formula fixed and then input either wildcards (for all depts) or a department name (for a single dept). Obviously my actual data and output criteria is much larger than this example.

I understand, but as i said you can keep up the formula as it is and insert the wildcards (*) as needed.

M.