SUMIFS Wildcard

[powelly]

Hi Guys,

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

Dept	Sub Dept	Sales
Dept A	Dept A1	100
Dept A	Dept A2	150
Dept B	Dept B1	500
Dept B	Dept B2	250
Dept B	Dept B3	150
Inputs
Dept	Dept A
Sub Dept	Dept A1
Output	100

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

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.