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.

 

[Peter_SSs]

seven fields, one per column in columns A through H

Columns A:H is eight columns, not seven. I'm assuming from your formula that eight is the correct number but please confirm.

I'm uploading an image of a sample dummy dataset

No image, but in any case an image is not much use as we cannot copy from it and it probably won't help with this problem as it appears to relate to whether values that look like numbers are actually numbers or text.

Instead, can you give us the dummy sample data (& the expected result(s)) with XL2BB?

 

[Funktion]

Columns A:H is eight columns, not seven. I'm assuming from your formula that eight is the correct number but please confirm.


No image, but in any case an image is not much use as we cannot copy from it and it probably won't help with this problem as it appears to relate to whether values that look like numbers are actually numbers or text.

Instead, can you give us the dummy sample data (& the expected result(s)) with XL2BB?

Hi Peter. Thank you for the reply. Yeah, I didn't explain that very well. My table is contained in eight columns. The first seven columns contain the account number fields (which is all I was thinking about when typing my entry), and the eighth column contains the charges / the sum range.

I tried uploading the picture and wasn't seeing it. So I tried it a second time and still didn't see it. I figured either something went wrong or it was just something I wouldn't be able to see. Ohm never mind. I just tried again and see there
s a warning that the file is too large. Curious. But as you say, that wouldn't have been very useful.

Yesterday, I downloaded the XL2BB file but have been unsuccessful in opening it. I'll try that again at some point today to see if I can get it to cooperate.

Stay tuned...

 

[Peter_SSs]

I downloaded the XL2BB file but have been unsuccessful in opening it.

You don't actually open it. Follow the instructions on the linked page carefully.

 

[Funktion]

You don't actually open it. Follow the instructions on the linked page carefully.

I believe I have successfully captured the Mini Sheet.

Dummy Data for Funktion 2023.10.08.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Enter desired fields here
2	Acct. Type	Fund	Dept.	Program	Acct. Code	Area	Project	Sub-project	TOTAL
3	E	15	21825	11817	450955	314332			$0.00	Should be $250.00 from row 7
4
5	Dataset
6	Acct. Type	Fund	Dept.	Program	Account Code	Area	Project	Sub-project	Charge
7	E	15	21825	11817	420955	314332			$250.00		SAMPLES OF OTHER POSSIBLE OUTCOMES
8	E	11	21825	21840	562806	314332			$4,000.00
9	E	11	21825	21840	562806	314332			$4,000.00		Acct. Type	Fund	Dept.	Program	Acct. Code	Area	Project	Sub-project	TOTAL
10	E	13	21825	00013	569915	314332	05715	702	$880.00		E	15	21825	11817	450955	314332			$250.00
11	E	13	21825	00013	562806	314332	06268	702	$4,000.00
12	E	13	21825	00013	562806	314332	06268	702	$4,000.00
13	E	11	21825	21840	562806	314332			$2,000.00		Acct. Type	Fund	Dept.	Program	Acct. Code	Area	Project	Sub-project	TOTAL
14	E	11	21825	21840	562806	314332			$2,000.00		E	11	21825	21840	562806	314332			$12,000.00
15	E	13	21825	00013	562806	314332	05715	702	$2,000.00
16	E	13	21825	00013	562806	314332	05715	702	$2,000.00
17	E	13	21825	00013	562806	314332	05715	702	$2,000.00		Acct. Type	Fund	Dept.	Program	Acct. Code	Area	Project	Sub-project	TOTAL
18	E	13	21825	00013	562806	314332	05715	702	$2,000.00		E	11	21825	00001	543259	314332			$1,909.80
19	E	13	21825	00013	562806	314332	06268	702	$2,000.00
20	E	13	21825	00013	562806	314332	06268	702	$2,000.00
21	E	13	21825	00013	569915	314332	05715	702	$110.00		Acct. Type	Fund	Dept.	Program	Acct. Code	Area	Project	Sub-project	TOTAL
22	E	11	21825	00001	543259	314332			$720.00		E	11	21825	00013	569915	314332	05715	702	$880.00
23	E	11	21825	00001	543259	314332			$720.00
24	E	11	21825	00001	543259	314332			$412.20
25	E	11	21825	00001	543259	314332			$57.60		Acct. Type	Fund	Dept.	Program	Acct. Code	Area	Project	Sub-project	TOTAL
26											E	11	21825	21840	562806	314332	05715	702	$8,000.00
27
28
29											Acct. Type	Fund	Dept.	Program	Acct. Code	Area	Project	Sub-project	TOTAL
30											E	13	21825	00013	562806	314332	06268	702	$12,000.00
31
32
33											Acct. Type	Fund	Dept.	Program	Acct. Code	Area	Project	Sub-project	TOTAL
34																314332			$35,149.80	ALL CHARGES, as they all have the Area 314332
35
36
37											Acct. Type	Fund	Dept.	Program	Acct. Code	Area	Project	Sub-project	TOTAL
38																		702	$20,990.00	ALL CHARGES WITH 702 Sub-project
39
40
41											Acct. Type	Fund	Dept.	Program	Acct. Code	Area	Project	Sub-project	TOTAL
42																	05715		$8,990.00	ALL CHARGES WITH 05715 Project
43
Sheet1

Cell Formulas
Range	Formula
I3	I3	=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&"")
S14	S14	=I8+I9+I13+I14
S18	S18	=I22+I23+I24+I25
S22	S22	=I10
S26	S26	=I15+I16+I17+I18
S30	S30	=I11+I12+I19+I20
S34	S34	=SUM(I7:I25)
S38	S38	=I10+I11+I12+I15+I16+I17+I18+I19+I20+I21
S42	S42	=I10+I15+I16+I17+I18+I21

 

[Dave Patton]

Ensure you specify the correct criteria; I edited one.

Sumifs.xlsm
	A	B	C	D	E	F	G	H	I
2	Acct. Type	Fund	Dept.	Program	Acct. Code	Area	Project	Sub-project	TOTAL
3	E	15	21825	11817	420955	314332			250
6e

Cell Formulas
Range	Formula
I3	I3	=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)

 

[Funktion]

Ensure you specify the correct criteria; I edited one.

Sumifs.xlsm
	A	B	C	D	E	F	G	H	I
2	Acct. Type	Fund	Dept.	Program	Acct. Code	Area	Project	Sub-project	TOTAL
3	E	15	21825	11817	420955	314332			250
6e

Cell Formulas
Range	Formula
I3	I3	=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)

Oops. Thank you for catching that, Dave. Now to get it to work with only one of the fields populated. Let me know if you have any suggestions for that. I'm still at a loss.

 

[Dave Patton]

"Thank you for catching that, Dave. Now to get it to work with only one of the fields populated. Let me know if you have any suggestions for that. I'm still at a loss."

Please review the other edits to your formula and try the changes to the other columns. Does the formula now work correctly?

 

[Funktion]

"Thank you for catching that, Dave. Now to get it to work with only one of the fields populated. Let me know if you have any suggestions for that. I'm still at a loss."

Please review the other edits to your formula and try the changes to the other columns. Does the formula now work correctly?

Oh, sorry. I didn't realize you provided an edited formula too. I just plugged that into my sheet and it's still only working if all the fields match exactly. I wish to be able to enter only one field, say the sub-project of 702, leaving all the other fields blank in the entry/yellow row, and have it return the sum of ALL charges that have 702 as a sub-project, regardless of the rest of the account number. In that case, the total returned should be $20,990.00. But when I fill in only the 702 in my entry row, it returns a zero.

 

[Funktion]

Oh, sorry. I didn't realize you provided an edited formula too. I just plugged that into my sheet and it's still only working if all the fields match exactly. I wish to be able to enter only one field, say the sub-project of 702, leaving all the other fields blank in the entry/yellow row, and have it return the sum of ALL charges that have 702 as a sub-project, regardless of the rest of the account number. In that case, the total returned should be $20,990.00. But when I fill in only the 702 in my entry row, it returns a zero.

And that Sub-project of 702 is just the sample I'm giving. I'd like this to work with any of the fields. If I were to fill in only the Fund field with 11, I'd want the return to be ALL charges with Fund 11, which would be $13,909.80 from my table. And the results would update every time I populate an additional field. So, if I were to populate Fund 13 and Acct. Code 562806, the result would be $20,000.
(and yes, I did just notice an error in my samples. There is no 11 21825 21840 562806 314332 05715 702 in my table. That was meant to be 13 21825 00013 562806 314332 05715 702. And the fund on the one above that should also be a 13. My bad.)