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...

 

[jasonb75]

See if this works. In theory it should but I haven't tested it.

=SUM(F2,PRODUCT(H2,IF(B2="IHC",3,1)),N2)

 

[Toadstool]

Hi RattlingCarp3408,

I'm finding it tricky following the requirement but aren't you just treating the "-" as if it's a zero?

RattlingCarp3048.xlsx
	B				F		H						N		P
1	IHC				2		2						2		10
2	IHC				1		2						3		10
3	IHC				-		2						1		7
4	IHC				1		-						7		8
5	IHC				1		1						-		4
6					2		2						2		6
7					1		2						3		6
8					-		2						1		3
9					1		-						7		8
10					1		1						-		2
Sheet1

Cell Formulas
Range	Formula
F3,N10,H9,F8,N5,H4	F3	="-"
P1:P10	P1	=IF(B1="IHC",(N(F1)+(N(H1)*3)+N(N1)),(N(F1)+N(H1)+N(N1)))

 

[DanteAmor]

deleted

 

[DanteAmor]

How about:

varios 28ene2022.xlsm
	B				F		H						N		P
1
2	IHC				2		2						2		10
3	IHC				1		2						3		10
4	IHC				-		2						1		7
5	IHC				1		-						7		8
6	IHC				1		1						-		4
7					2		2						2		6
8					1		2						3		6
9					-		2						1		3
10					1		-						7		8
11					1		1						-		2
Hoja20

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

 

[RattlingCarp3048]

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.

 

[kevin9999]

Does this give you what you want?

Book1.xlsx
	B				F		H						N		P
1
2	IHC				-		40						-		120
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(B2="IHC",(H2*3)+SUM(F2)+SUM(N2),SUM(F2)+SUM(H2)+SUM(N2))

 

[RattlingCarp3048]

Does this give you what you want?

Book1.xlsx
	B				F		H						N		P
1
2	IHC				-		40						-		120
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(B2="IHC",(H2*3)+SUM(F2)+SUM(N2),SUM(F2)+SUM(H2)+SUM(N2))

=IF(B2="IHC",(H2*3)+SUM(F2)+SUM(N2),SUM(F2)+SUM(H2)+SUM(N2))

This that you gave is the closest ive been all day. in about 98% of the rows it is working perfectly. What I dont understand is the instances where it didnt. I double checked that the formulas in the columns are the same all the way down and basic formatting of the cells is the same. so why would this be happening.... why the #value! error?

 

[RattlingCarp3048]

Does this give you what you want?

Book1.xlsx
	B				F		H						N		P
1
2	IHC				-		40						-		120
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(B2="IHC",(H2*3)+SUM(F2)+SUM(N2),SUM(F2)+SUM(H2)+SUM(N2))

oh i see.... its because of this... its trying to multiply H*3 when there is no data in H because B=IHC.

 

[kevin9999]

oh i see.... its because of this... its trying to multiply H*3 when there is no data in H because B=IHC.

View attachment 56410

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))