Formula to add amounts based on cell above

[Mldeuser]

Hello

I am looking for a formula that will look in column E for stx if found it will look at the cell above for the word sundry if found it will add the amount in column I for the stx row.

Cell E2 = stx
Cell E1 = sundry
Then add cell I2

I then need another formula that would look in column E for the word sundry if found it would look at the cell below for the word stx if none is found it would add the amount in column I

Thank you

 

[Joe4]

Please show us a sample of your data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Mldeuser]

Here is a sample

Thank you for your time on this

 

[awoohaw]

Please clarify the data and the expected results. Thiis what I come up with:

Mr excel questions 66.xlsm
	A	B	C	D	E	F	G	H	I
1	1st formula	2nd formula			a
2	675	675			b				675
3					sundry
4					stx
5					c
6					stx
7					g
8					f
Mldeuser

Cell Formulas
Range	Formula
A2	A2	=IF(AND(ISNUMBER(MATCH("stx",$E$1:$E$11,0)),"sundry"=INDEX($E$1:$E$11,MATCH("stx",$E$1:$E$11,0)-1,1)),I2,"")
B2	B2	=IF(AND(ISNUMBER(MATCH("sundry",$E$1:$E$11,0)),"stx"=INDEX($E$1:$E$11,MATCH("sundry",$E$1:$E$11,0)+1,1)),I2,"")

Mr Excel has a tool called xl2bb (link below) that allows you to post mini worksheets. This means the forum doesn't have to guess at your scenario and get your requirement incorrect which wastes everyones time. Please help the forum help you. If you cannot use the add in, post the data in a table (label the rows and columns please). Of course sanitize your data for privacy.

Thanks in advance!


Edit: Just saw your screen shot. Are we looking in the same ROW intersection with Column E or all of Column E?


try this:

Mr excel questions 66.xlsm
	A	B	C	D	E	F	G	H	I
10
11	1st formula	2nd formula			a				25
12					b				29
13					sundry				26
14	14				stx				14
15					c				25
16		22			sundry				22
17					g				29
18					f				12
Mldeuser

Cell Formulas
Range	Formula
A12:A18	A12	=IF(AND(E12="stx",E11="sundry"),I12,"")
B12:B18	B12	=IF(AND(E12="sundry",E13<>"stx"),I12,"")

 

[Mldeuser]

Hello

The amount for the first formula should be 7.99 as there is only one row with stx in column e that has sunry in the cell above.
The amount of the second formula should be 3,082.82 as there is only one row with sundry in column e that there is not a row below it with stx in column e

Thank you

 

[awoohaw]

Hello

The amount for the first formula should be 7.99 as there is only one row with stx in column e that has sunry in the cell above.
The amount of the second formula should be 3,082.82 as there is only one row with sundry in column e that there is not a row below it with stx in column e

Thank you

well, use the formulas I have above in your workbook and see if they work. I'm not going guess at recreating a scenario verbatim when you can paste it in a table.

 

[Mldeuser]

Hello

The formulas are not working, the information in the rows will change from month to month so the cells containing stx with the above cells containing sundry will not be the same each month same for formula two

Thank you

 

[awoohaw]

Hello

The formulas are not working, the information in the rows will change from month to month so the cells containing stx with the above cells containing sundry will not be the same each month same for formula two

Thank you

then please post your data, xl2bb or in a table (labeling rows and columns). The formulas I gave you reference the exact columns your written text states.
Help the forum help you!

 

[Mldeuser]

Hello

Work will not allow me to download the addin. I hope the below helps with what I am trying to do with the two formulas. Since there are more rows with ele in column E I thought it would be best to use this in the formula.

Thank you

 

[awoohaw]

Please highlight the area, click copy. Come to this forum, and paste into a table. If you can't label row/columns just says what the top left cell address is.