Calculations from multiple cells

jasriel

New Member
Joined
Apr 7, 2016
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Attachments

  • Capture.JPG
    Capture.JPG
    58.5 KB · Views: 9

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hi, does this solve it for you ?

Book1
ABCDEFG
2StartEnter Х
35x
4200xTotal Start
5200x
6210x5420
7213x
8224x
9224x
10224
11224x
12224
13221x
14224
15224xTotal After Deductions
16149x
17149x4748
18224x
19224x
20224x
21224x
22224x
23221x
24210x
25224x
26216x
27224x
2816x
2977x
3032x
3169x
3296x
Sheet1
Cell Formulas
RangeFormula
F6F6=SUM(A3:A32)
F17F17=SUMIF(B3:B32,"x",A3:A32)


(sorry, I see its backwards to what you want .. will try again..)
 
Upvote 0
Book1
ABCDEF
1
2StartEnter Х
35
4200Total Start
5200
62105420
7213
8224
9224
10224
11224
12224
13221
14224
15224Total After Deductions
16149
17149x5271
18224
19224
20224
21224
22224
23221
24210
25224
26216
27224
2816
2977
3032
3169
3296
Sheet1
Cell Formulas
RangeFormula
F6F6=SUM(A3:A32)
F17F17=SUMIF(B3:B32,"<>x",A3:A32)
 
Upvote 0
Solution
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?
 
Upvote 0
I see it working, but I am confused on why it is working I guess.
 
Upvote 0
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
 
Upvote 0
youre welcome, glad we could assist.

thanks for the feedback

Rob
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,246
Members
452,623
Latest member
cliftonhandyman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top