Calculations from multiple cells

[jasriel]

Hi,

I want to know if it is possible to have numbers in multiple cells (a1 through a32) then place an "x" next to each of those cells (b1 though b32) and then each cell that has an "x" next to it deduct the number in column A's cells from a total number? Please reference the attachment as I know I didn't explain it well.

Basically cells in column A to subtract from D3 (merged cells) only if there is an X entered in a corresponding cell in column B. So for example If X entered in B3 Deduct cell A3 from D3 and total appears in D14.

 

[RobP]

Hi, does this solve it for you ?

Book1
	A	B	C	D	E	F	G
2	Start	Enter Х
3	5	x
4	200	x				Total Start
5	200	x
6	210	x				5420
7	213	x
8	224	x
9	224	x
10	224
11	224	x
12	224
13	221	x
14	224
15	224	x				Total After Deductions
16	149	x
17	149	x				4748
18	224	x
19	224	x
20	224	x
21	224	x
22	224	x
23	221	x
24	210	x
25	224	x
26	216	x
27	224	x
28	16	x
29	77	x
30	32	x
31	69	x
32	96	x
Sheet1

Cell Formulas
Range	Formula
F6	F6	=SUM(A3:A32)
F17	F17	=SUMIF(B3:B32,"x",A3:A32)

(sorry, I see its backwards to what you want .. will try again..)

 

[RobP]

Book1
	A	B	C	D	E	F
1
2	Start	Enter Х
3	5
4	200					Total Start
5	200
6	210					5420
7	213
8	224
9	224
10	224
11	224
12	224
13	221
14	224
15	224					Total After Deductions
16	149
17	149	x				5271
18	224
19	224
20	224
21	224
22	224
23	221
24	210
25	224
26	216
27	224
28	16
29	77
30	32
31	69
32	96
Sheet1

Cell Formulas
Range	Formula
F6	F6	=SUM(A3:A32)
F17	F17	=SUMIF(B3:B32,"<>x",A3:A32)

 

[jasriel]

I see how you are working the formula (still kinda above my head), however, the part I am not seeing is where is the reference in the formula that subtracts all the entries from F6?

 

[jasriel]

I see it working, but I am confused on why it is working I guess.

 

[RobP]

ah ok , well I'm not subtracting as such. the formula says 'Sum Col A if there is NOT an x in Col B.

So if there is an X there, that number is ignored from column A.

F6 has no input to the formula in F17 in this case. its just there to show you what the total of all ColA should be.

make sense ?

Rob

 

[jasriel]

Yes, I understand now. Thank you for the clarification.

 

[RobP]

youre welcome, glad we could assist.

thanks for the feedback

Rob