CODEWITHSUNDEEP
excelexcel-formula
LiamNeesonFan
LiamNeesonFan

Reputation: 2813

Sum-if function: the value from one column and have the function sum the cells x places to the right/left of the original column

I would like help trying to figure out how to sumif a value from one column and then sum all the values to the left of the column.

For example, if column B has a value of 1 then sum all the cells one space to the left of column B that have a 1 in column B.

I tried using VLookup or index/match but could not get the formulas to work. I have attached an image below which might make it clearer. enter image description here

Upvotes: 0

Views: 1504

Answers (2)

Glitch_Doctor
Glitch_Doctor

Reputation: 3034

=SUMIF(B2:B14,1,A2:A14)

So the breakdown of this formula is: SUMIF(range,criteria,[sum range]). The first range, is the range that you will check against the criteria that you set (if you wanted to have a cell to change what you are summing, replace 1 with that cell instead) the [sum range] is simply the range that you want to be added up but this will only add the rows where the range,criteria are true.

Upvotes: 1

nightcrawler23
nightcrawler23

Reputation: 2066

Try this. -1 in the OFFSET is for 1 column to the left.

=SUMPRODUCT((B2:B18=1)*OFFSET(B2:B18,0,-1))

Upvotes: 0

Related Questions