Need help with adding IF function to existing formula.

[vvthegoodlifevv]

Hi, all,

I'm using: {=SUM(--LEFT(IF(A2:A18="",0,A2:A18),1))}

I would like to add an IF function to this formula so it only sums the left of cells A2:A18 IF cells B2:B18 are "108". Does this make sense?

 

[Fluff]

Hi & welcome to MrExcel.
How about
=SUM(--LEFT(IF((A2:A18="")+(B2:B18<>108),0,A2:A18),1))

 

[vvthegoodlifevv]

Thank you, @Fluff . Something is a miss though. The formula returned a 0. Screenshot below. (Note: without {...} it returned a Value error.)

 

[vvthegoodlifevv]

By the way, the cell ranges given previously were hypothetical. Reply above shows actual cells, and ranges being used. Thank you in advance.

 

[Fluff]

In that case, can you please post some sample data using the XL2BB add-in, along with the expected result.

 

[vvthegoodlifevv]

Sample Data:

Book1
	D	E	F
6			Items
7			39
8	106	1I: STUCCO
9	108	5I:PAINT
10	108	1I: DOOR
11	108	2I:CRACK
12	108	1I:KITCHEN
13	108	1I: GROUT
14	108	1I:OUTLET
15	108	2I:EXT
16	108	8I: GAPS
17	108	4I:TILE
18	108	1I:TOILET
19	108	1I:ENTRY
20	108	2I: LAWN
21	108	1I:SHOWER
22	108	1I:BATH
23	108	2I:SEWER
24	108	2I: CARPET
25	108	2I: IRRIG.
26	112	2I: DEFECTIVE
27	112	1I:FRONT
28	112	1I:MANDOOR
29	112	29I: PENDING
30	112	36I: PAINT
31	112	1I:LEAK
32	112	1I: EXT
33	112	1I:STAIRS
34	112	4I:DRYWALL
35	112	1I:STAIRCASE
36	112	1I:CRACKS
37	112	1I: NO
38	112	2I:PAINT
39	112	4I:MBATH
40	112	1I:EXT
41	112	1I:TOILET
42	112	2I:FAUCET
43	112	10I:TILE
44	112	1I:KIT
45	112	7I:STUCCO
46	112	1I: GARBAGE
Sheet1

Cell Formulas
Range	Formula
F7	F7	=SUM(--LEFT(IF((E8:E46="")+(D8:D46<>112),0,E8:E46),1))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Expected Result: 108

 

[Fluff]

Can you please explain how you get 108 from that data?

 

[vvthegoodlifevv]

Rows 26-46 Column D, are all 112. Column E, left side of the cell are numbers that the formula should return a sum for. So, left side of the cells of rows 26-46 should add up to 108.

 

[Fluff]

Your formula is summing the first character of Col E if col D is 112, which comes to 39.

 

[vvthegoodlifevv]

In my XL2BB post above, I copied the data to a new, standard workbook, and the formula returns only the furthest left digit in the range from E26-E46. Interestingly, what is captured below is from the sheet containing all formatting used on an Excel macro-enabled workbook pivot table.

v4 OPEN CSR Report.xlsm
	I	J	K	L
2		TOTAL	Leaks	Items
3		93	12	0
4		67
5		18
6		5
7		3
8
9
10
11	SERVICE REP_01	CSR DESCRIPTION	CSR STATUS	CSR AGE
12	106	1I: STUCCO CRACKS	OPEN	23
13	108	5I:PAINT,BASEBOARDS,DRYWALL	OPEN	16
14	108	1I: DOOR STICKING	OPEN	16
15	108	2I:CRACK TILE,PAINT COLORS	OPEN	43
16	108	1I:KITCHEN FAUCET MOUNTED	OPEN	26
17	108	1I: GROUT	OPEN	23
18	108	1I:OUTLET @ EXT. REAR	OPEN	30
19	108	2I:EXT VENTS, FLOOR SQUEAK	OPEN	6
20	108	8I: GAPS/DOORS/CAB/PUDDLES	OPEN	69
21	108	4I:TILE/CHIPPED/CHIPPED	OPEN	37
22	108	1I:TOILET NOT SECURED	OPEN	3
23	108	1I:ENTRY LIGHT SWITCH	OPEN	15
24	108	2I: LAWN LEAKS	OPEN	15
25	108	1I:SHOWER NOT DRAINING	OPEN	63
26	108	1I:BATH FAN DAMPER CLICKS	OPEN	9
27	108	2I:SEWER BOX,AIR FLOW	OPEN	30
28	108	2I: CARPET COMING UP/RAISED	OPEN	14
29	108	2I: IRRIG.LEAK, BATHTUB	OPEN	51
30	112	2I: DEFECTIVE TILES X2	OPEN	167
31	112	1I:FRONT DOOR HARD TO CLOSE	OPEN	30
32	112	1I:MANDOOR LOCK STUCK	OPEN	14
33	112	29I: PENDING W/T ITEMS	OPEN	42
34	112	36I: PAINT,CARPET,STAIRS	OPEN	42
35	112	1I:LEAK,GARAGE CRACK,STUCCO	OPEN	495
36	112	1I: EXT. LEAK	OPEN	2
37	112	1I:STAIRS LOOSE	OPEN	23
38	112	4I:DRYWALL,CEILING,COUNTERS	OPEN	49
39	112	1I:STAIRCASE LOOSE	OPEN	49
40	112	1I:CRACKS/SEPARATION	OPEN	14
41	112	1I: NO HEAT U/S	OPEN	2
42	112	2I:PAINT,ELECTRICAL	OPEN	19
43	112	4I:MBATH&LAUNDRY TILE,GARAG	OPEN	26
44	112	1I:EXT REAR FENCE	OPEN	26
45	112	1I:TOILET WATER DECREASING	OPEN	20
46	112	2I:FAUCET LEAK,FAUCET	OPEN	15
47	112	10I:TILE,PAINT,CAULK,CAB,DR	OPEN	2
48	112	1I:KIT FAUCET LEAKING	OPEN	1
49	112	7I:STUCCO,DRYWALL,DOOR,A/C	OPEN	48
50	112	1I: GARBAGE DISPOSAL LEAK	OPEN	2
Pivot

Cell Formulas
Range	Formula
K3	K3	=COUNTIF(J12:J180000,"*Leak*")
L3	L3	=SUM(--LEFT(IF((J12:J50="")+(I12:I50<>112),0,J12:J50),1))
J3	J3	=COUNTA(K12:K180000)
J4	J4	=COUNTIFS(L12:L180000,"<30")
J5	J5	=COUNTIFS(L12:L180000,">29",L12:L180000,"<60")
J6	J6	=COUNTIFS(L12:L180000,">59",L12:L180000,"<120")
J7	J7	=COUNTIFS(L12:L180000,">119")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J12:J180000	Cell Value	contains "*Leak*"	text	NO
L12:L180000	Cell Value	>119	text	NO
L12:L180000	Cell Value	between 60 and 119	text	NO
L12:L180000	Cell Value	between 31 and 59	text	NO