Sumifs with any one or more of multiple criteria

[Funktion]

Hi, I'm using Microsoft Office 365 on a managed, Windows-based machine.

I have a dataset (A6:I25) with seven fields, one per column in columns A through H, making up an account number. Not all account numbers contain all seven fields. There are charges in column I for each distinct account number combination. At the top (in row 3), I have an entry section where I can enter in any of the criteria found within the account numbers. I would like to be able to get a sum if I enter just one, or two, or any number of those criterium. I don't want it to look for a match to those blank cells in the dataset. But rather, I want it to ignore that field and return results for only the field(s) entered.

I've asked a somewhat similar question before and the solution to that, which was adding &"" to the end of some of the cell references, doesn't seem to be working here. The formula I'm trying is:
=SUMIFS(I7:I25,A7:A25,A3&"",B7:B25,B3&"",C7:C25,C3&"",D7:D25,D3&"",E7:E25,E3&"",F7:F25,F3&"",G7:G25,G3&"",H7:H25,H3&"")

I'm uploading an image of a sample dummy dataset with some color coding to group the distinct account number combinations. Pay particular attention to the bottom three sample on the right side of the sheet.

 

[Dave Patton]

You didn't show what you tried!

You could try the following
- make the data a Table
- try the formula
- only 702 selected for Sub-project the sum shows

or
try formulas with Filter

Sumifs.xlsm
	A	B	C	D	E	F	G	H	I
5	Dataset								20,990.00
6	Acct. Type	Fund	Dept.	Program	Account Code	Area	Project	Sub-project	Charge
10	E	13	21825	13	569915	314332	5715	702	880
11	E	13	21825	13	562806	314332	6268	702	4000
12	E	13	21825	13	562806	314332	6268	702	4000
15	E	13	21825	13	562806	314332	5715	702	2000
16	E	13	21825	13	562806	314332	5715	702	2000
17	E	13	21825	13	562806	314332	5715	702	2000
18	E	13	21825	13	562806	314332	5715	702	2000
19	E	13	21825	13	562806	314332	6268	702	2000
20	E	13	21825	13	562806	314332	6268	702	2000
21	E	13	21825	13	569915	314332	5715	702	110
6e

Cell Formulas
Range	Formula
I5	I5	=SUBTOTAL(109,Table4[Charge])

 

[Dave Patton]

Sumifs.xlsm
	A	B	C	D	E	F	G	H	I
4
5	Dataset								13,909.80
6	Acct. Type	Fund	Dept.	Program	Account Code	Area	Project	Sub-project	Charge
8	E	11	21825	21840	562806	314332			4000
9	E	11	21825	21840	562806	314332			4000
13	E	11	21825	21840	562806	314332			2000
14	E	11	21825	21840	562806	314332			2000
22	E	11	21825	1	543259	314332			720
23	E	11	21825	1	543259	314332			720
24	E	11	21825	1	543259	314332			412.2
25	E	11	21825	1	543259	314332			57.6
26
6e

Cell Formulas
Range	Formula
I5	I5	=SUBTOTAL(109,Table4[Charge])

 

[Dave Patton]

With just 702 selected in Drop-Down for Sub_Project or by Sumifs

Sumifs.xlsm
	A	B	C	D	E	F	G	H	I
5	Dataset								20,990.00
6	Acct. Type	Fund	Dept.	Program	Account Code	Area	Project	Sub-project	Charge
10	E	13	21825	13	569915	314332	5715	702	880.00
11	E	13	21825	13	562806	314332	6268	702	4,000.00
12	E	13	21825	13	562806	314332	6268	702	4,000.00
15	E	13	21825	13	562806	314332	5715	702	2,000.00
16	E	13	21825	13	562806	314332	5715	702	2,000.00
17	E	13	21825	13	562806	314332	5715	702	2,000.00
18	E	13	21825	13	562806	314332	5715	702	2,000.00
19	E	13	21825	13	562806	314332	6268	702	2,000.00
20	E	13	21825	13	562806	314332	6268	702	2,000.00
21	E	13	21825	13	569915	314332	5715	702	110.00
26
27							20,990.00	702
28
6e

Cell Formulas
Range	Formula
I5	I5	=SUBTOTAL(109,Table4[Charge])
G27	G27	=SUMIFS(Table4[Charge],Table4[Sub-project],H27)