Complex if/iferror formula

[RattlingCarp3048]

HELP... i need to make a formula that is a bit more complex than I am used to. if there is a much simpler way than my initial approach i am open to alternative methods.

Columns F, H, and N each contain their own formula. They either result in a number or a "-" as a result of an error.

I need Column P to = F+H+N.

If Column B = "IHC" then calculate F+(H*3)+N instead.

If any one or combination of F, H, or N = "-" calculate the others instead. For instance

If B = IHC then calculate F+H*3+N otherwise calculate base

If B = IHC then calculate F+H*3+N

If F = - then calculate H+N

If H = - then calculate F+N

If N = - then calculate F+H

If F+H = - then calculate N

If F+N = - then calculate H

If H+N= - then calculate F

If B = IHC then calculate H+N

If B = IHC then calculate H+N etc...

 

[RattlingCarp3048]

Understood. Try this instead

Book1.xlsx
	B				F		H						N		P
1
2	IHC				-		-						-		0
3	Meat				32.0		-						-		32
4	Veggies				-		15						-		15
5	IHC				23.0		34						12		137
6	Veggies												15		15
7	Veggies						24						32		56
Sheet1

Cell Formulas
Range	Formula
P2:P7	P2	=IF(AND(B2="IHC",ISNUMBER(H2)),(H2*3)+SUM(F2)+SUM(N2),SUM(F2)+SUM(H2)+SUM(N2))

There we go! looks like that was it. Whew. i never would have gotten that on my own. Question: i tried a variation of the Sum function to no avail. in this particular format why would simply stating P=F2+H2+N2 not work? is it because the =sum is looking for the specific value of each cell?

 

[kevin9999]

There we go! looks like that was it. Whew. i never would have gotten that on my own. Question: i tried a variation of the Sum function to no avail. in this particular format why would simply stating P=F2+H2+N2 not work? is it because the =sum is looking for the specific value of each cell?

It's because Excel's Sum() Function ignores text.

 

[RattlingCarp3048]

It's because Excel's Sum() Function ignores text.

OHHHHHHHHH i never new that! i always thought it just added whatever was in the cell. Either way Thank you!!

 

[kevin9999]

OHHHHHHHHH i never new that! i always thought it just added whatever was in the cell. Either way Thank you!!

You're welcome and thanks for the feedback ?

 

[DanteAmor]

Sorry I wasnt more clear. Here is a sample picture of data. Names and titles changed for confidentiality. F,H,N are different departments. Each employee has a primary department they are assigned to but some are cross-trained to work in other areas. so the data per person is constantly changing between the columns. Basically what i am trying to do is add the columns that do have data and ignore the ones that are "-". The dashes do not represent 0. Columns F, H, N each have their own individual formulas in them that state if the return value is 0 or error, show a dash instead. This is just to help keep the sheet cleaner in appearance.

Did you try the formula from post #5?
Meets all your specifications.

varios 28ene2022.xlsm
	A	B				F		H						N		P
1
2		IHC				-		40						-		120
3		Meat				32		-						-		32
4		Veggies				-		15						-		15
5		IHC				23		34						12		137
6		Veggies												15		15
7		Veggies						24						32		56
Hoja3

Cell Formulas
Range	Formula
P2:P7	P2	=SUM(F2,N(H2)*IF(B2="IHC",3,1),N2)