Can't get multiple criteria for SUMIFS to work like I want

[Nanaia]

My goal is to modify (or change completely) the formula located in cells V3, W3, X3, Y3 within the CHEATER tab so that they look for the data in both column T and also the number we will enter into cell S3.
What this chart does is count the number of ICP parts needed for each job. I want to make it so I can also identify the total number needed of each kind of ICP parts needed for each elevation not just the job as a whole. I thought I could add a VLOOKUP to the formula but I'm not sure how and what I've tried hasn't worked.
Since I can't easily copy and paste the information into this post, I've saved the file to a Share Drive and provided a link to it below.


https://1drv.ms/x/s!AskYMgFiAqmA0BsSdR9vLkfM18xO

 

[Steph_pmg]

Hi ALL,
Im having a similar issue. I need to reference column B made up of GL #'s and dashes and sum the values for each year for GL #'s 560 and 570 but per location.
Location is the 2 numbers in the GL 's.

Example

Column B Column C Column D Column E
560-01-0010 BDF 109,334 =SUMIFS(D7:D136,B7:B136,B7,B7:B136,B72)
570-01-0010 BDF 58,365
560-12-1201 IMMUN 511,522
560-00-0000 2,651,615
570-12-1201 6,262
570-00-0000 61,916

I tried the SUMIFS formula above and =SUMIF(B7:B136,"560-00",D7:D136)+SUMIF(B7:B136,"570-00",D7:D136)

Not working

 

[jtakw]

Hi,

Try this:

Book1
	B	C	D	E	F
7	560-01-0010	BDF	109334		2713531
8	570-01-0010	BDF	58365
9	560-12-1201	IMMUN	511522
10	560-00-0000		2651615
11	570-12-1201		6262
12	570-00-0000		61916
Sheet630

Cell Formulas
Range	Formula
F7	=SUM(SUMIF(B7:B136,{"560-00-*","570-00-*"},D7:D136))

 

[Steph_pmg]

Hmm, that did not work. The results doubled the actual values and put values where there should be zeroes.

Maybe a nested IF function might work better...

 

[jtakw]

Perhaps you mean Separate sum, if not, you need to Explain exactly what you mean, And for your sample in your Post # 21, What is/are the expected results ??

Book1
	B	C	D	E	F	G	H
6					for 560-00	for 570-00	for both
7	560-01-0010	BDF	109334		2651615	61916	2713531
8	570-01-0010	BDF	58365
9	560-12-1201	IMMUN	511522
10	560-00-0000		2651615
11	570-12-1201		6262
12	570-00-0000		61916
Sheet630

Cell Formulas
Range	Formula
F7	=SUMIF(B7:B136,"560-00-*",D7:D136)
G7	=SUMIF(B7:B136,"570-00-*",D7:D136)
H7	=SUM(SUMIF(B7:B136,{"560-00-*","570-00-*"},D7:D136))

 

[Steph_pmg]

1 Column B Column C Column D Column E
2 560-01-0010 BDF 109,334
3 570-01-0010 BDF 58,365 =sum(Column D Row 2 + Column D Row 3)
4 560-12-1201 IMMUN 511,522
5 560-00-0000 Accrual 2,651,615
6 570-12-1201 IMMUN 6,262 =sum(Column D Row 4 + Column D Row 6)
7 570-00-0000 Accrual 61,91 =sum(Column D Row 5 + Column D Row 7)

Expected Outcome :
Total Revenue should not change. All we are doing is combining the 2 account totals for each division.

When I used your formulas the total revenue changed. Divisions that had zero revenue before combing the account totals, suddenly had revenue totals using those formulas.

Something is throwing the formula off. I think its the dashes with the numbers. Not to sure...

PS: sorry the spacing keeps messing up when i post.

 

[jtakw]

You didn't describe How your data is set up, and Where the result cells should be, you may need to play around with the cell references.

G7:I7 formulas have the "account" hard-coded.
G8 formula copied to I8 using cell references for "account".

Book1
	B	C	D	E	F	G	H	I
6					Account	00	01	12
7	560-01-0010	BDF	109334		Total	2713531	167699	517784
8	570-01-0010	BDF	58365			2713531	167699	517784
9	560-12-1201	IMMUN	511522
10	560-00-0000		2651615
11	570-12-1201		6262
12	570-00-0000		61916
Sheet630

Cell Formulas
Range	Formula
G7	=SUMIF(B7:B136,"*-00-*",D7:D136)
G8	=SUMIF($B7:$B136,"*-"&G6&"-*",$D7:$D136)
H7	=SUMIF(B7:B136,"*-01-*",D7:D136)
I7	=SUMIF(B7:B136,"*-12-*",D7:D136)