If column E contains x - do this, if contains y - so that, etc

[mtaylor]

Hi - thanks for looking.

I have responses being inputted to a sheet and I want formulas run depending on the text in cells in Column E

The different responses are:

Double Sided, A4

Double Sided, A5/2 On 1pg

Double Sided

Single Sided

Double Sided

Single Sided

Then the formulas in column H are:

=((ROUNDUP(F2/2)*G2))

=((ROUNDUP(F3/4)*G3))

=((ROUNDUP(F4/2)*G4))

=G5*F5

=((ROUNDUP(F6/2)*G6))

=G7*F7

So for example:

If E3 says "Double Sided, A4" I want the formula "=((ROUNDUP(F2/2)*G2))" to be run etc

Hopefully this makes a little sense?

Thanks in advance

 

[Jamie McMillan]

Hello,

=IF(K13="Double Sided, A4",((ROUNDUP(F2/2)*G2)),"")

The "" at the end is what is shown if it is not equal (an empty cell), you can change that to whatever you want.

Jamie

 

[mtaylor]

Hello,

=IF(K13="Double Sided, A4",((ROUNDUP(F2/2)*G2)),"")

The "" at the end is what is shown if it is not equal (an empty cell), you can change that to whatever you want.

Jamie

Cheers Jamie,

I had a look and tried the below: with IFS function:

=IFS(E2="Double Sided, A4",((ROUNDUP((F2/2)*G2)),""),(E2="Double Sided, A5/2 On 1pg",((ROUNDUP(F2/4)*G2)),"")E2=),(E2="Double Sided",((ROUNDUP(F2/2)*G2)),""),(E2="Single Sided",(G2*F2),"")

Thanks again

 

[mtaylor]

Cheers Jamie,

I had a look and tried the below: with IFS function:

=IFS(E2="Double Sided, A4",((ROUNDUP((F2/2)*G2)),""),(E2="Double Sided, A5/2 On 1pg",((ROUNDUP(F2/4)*G2)),"")E2=),(E2="Double Sided",((ROUNDUP(F2/2)*G2)),""),(E2="Single Sided",(G2*F2),"")

Thanks again

=IFS(E2="Double Sided, A4",((ROUNDUP((F2/2)*G2)),""),(E2="Double Sided, A5/2 On 1pg",((ROUNDUP(F2/4)*G2)),""),(E2="Double Sided",((ROUNDUP(F2/2)*G2)),""),(E2="Single Sided",(G2*F2),""))

 

[jtakw]

Hi,

I think this should do what you want:

Book3.xlsx
	E	F	G	H
1		#Original	#Copy	#Printouts
2	Double Sided, A4	2	20	20
3	Double Sided, A5/2 On 1pg	8	84	168
4	Double Sided	3	84	168
5	Single Sided	3	26	78
6	Double Sided	3	10	20
7	Single Sided	2	6	12
Sheet1042

Cell Formulas
Range	Formula
H2:H7	H2	=ROUNDUP(F2/MAX((((ISNUMBER(SEARCH("2 On 1",E2))+ISNUMBER(SEARCH("Double",E2)))*2)),1),0)*G2

 

[jtakw]

Hi again, providing Shorter version of my formula in H2:

Book3.xlsx
	E	F	G	H	I
1		#Original	#Copy	#Printouts	#Printouts
2	Double Sided, A4	2	20	20	20
3	Double Sided, A5/2 On 1pg	8	84	168	168
4	Double Sided	3	84	168	168
5	Single Sided	3	26	78	78
6	Double Sided	3	10	20	20
7	Single Sided	2	6	12	12
Sheet1042

Cell Formulas
Range	Formula
H2:H7	H2	=ROUNDUP(F2/MAX(SUM(COUNTIF(E2,{"*2 On 1*","*Double*"}))*2,1),0)*G2
I2:I7	I2	=ROUNDUP(F2/MAX((((ISNUMBER(SEARCH("2 On 1",E2))+ISNUMBER(SEARCH("Double",E2)))*2)),1),0)*G2

 

[mtaylor]

Hi again, providing Shorter version of my formula in H2:

Book3.xlsx
	E	F	G	H	I
1		#Original	#Copy	#Printouts	#Printouts
2	Double Sided, A4	2	20	20	20
3	Double Sided, A5/2 On 1pg	8	84	168	168
4	Double Sided	3	84	168	168
5	Single Sided	3	26	78	78
6	Double Sided	3	10	20	20
7	Single Sided	2	6	12	12
Sheet1042

Cell Formulas
Range	Formula
H2:H7	H2	=ROUNDUP(F2/MAX(SUM(COUNTIF(E2,{"*2 On 1*","*Double*"}))*2,1),0)*G2
I2:I7	I2	=ROUNDUP(F2/MAX((((ISNUMBER(SEARCH("2 On 1",E2))+ISNUMBER(SEARCH("Double",E2)))*2)),1),0)*G2

Thank you,

I need all the formulas in one cell though, hence I was looking at the IFS function.

Where have I gone wrong here:

=IFS(E2="Double Sided, A4",((ROUNDUP((F2/2)*G2)),""),(E2="Double Sided, A5/2 On 1pg",((ROUNDUP(F2/4)*G2)),""),(E2="Double Sided",((ROUNDUP(F2/2)*G2)),""),(E2="Single Sided",(G2*F2),""))

 

[Alex Blakenburg]

Try this:

=IFS(E2="Double Sided, A4", ROUNDUP(F2/2*G2,0),
         E2="Double Sided, A5/2 On 1pg",ROUNDUP(F2/4*G2,0),
         E2="Double Sided",ROUNDUP(F2/2*G2,0),
         E2="Single Sided",(G2*F2),
         TRUE,"")

 

[mtaylor]

Try this:

=IFS(E2="Double Sided, A4", ROUNDUP(F2/2*G2,0),
         E2="Double Sided, A5/2 On 1pg",ROUNDUP(F2/4*G2,0),
         E2="Double Sided",ROUNDUP(F2/2*G2,0),
         E2="Single Sided",(G2*F2),
         TRUE,"")

Jackpot - Cheers Alex

 

[Alex Blakenburg]

No worries. Glad we could help.