'IF' query with two outcomes referencing another formula.

[Pr0ximitea]

Hi all,

I am putting together a spreadsheet that needs to be flexible with numbers but quick and easy to amend. I'm a bit poor at explaining so hopefully this will make sense!

I have a Capacity Cell (D5) - context is products that can go down a line in an hour.

Linked to this cell are 9 rows (simple =D$2 formula), each one representing an hour of the day, with two containing adjustments for breaktimes (/3 for 20 mins break, /2 for 30 mins break).

However, where certain products go down the line, the capacity decreases by 60 (e.g. 180 is the capacity per hour normally, reduces down to 120 when a certain product is being produced).

Therefore, what I want to do is have a cell reference the Capacity in D5, however if the word 'Silverside' appears in the Description box beside it, it is reduced by 60.

Below is the reference I attempted to use (also didn't work) - but the problem with this is that it would be static with 120 or 180 - I want it to reference what is in cell D5 on a normal basis, however when the word 'Silverside' appears in the H23:H28 section, I want it to deduct -60 from that value.

=IF(H23:H28="Silverside","120",IF(H23:H28<>"Silverside","180"))

Am I being overambitious or is this potentially a formula that someone could please share with me? Again, apologies for my poor explaining skills, and thank you in advance for any help!

 

[JamesCanale]

MrExcelPlayground16.xlsx
	C	D	E	F	G	H
5		180
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23	120					Fred
24						George
25						Silverside
26						Biff
27						Mary
28						Sally
Sheet26

Cell Formulas
Range	Formula
C23	C23	=IF(SUM(--(H23:H28="Silverside"))>0,D5-60,D5)

 

[Pr0ximitea]

MrExcelPlayground16.xlsx
	C	D	E	F	G	H
5		180
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23	120					Fred
24						George
25						Silverside
26						Biff
27						Mary
28						Sally
Sheet26

Cell Formulas
Range	Formula
C23	C23	=IF(SUM(--(H23:H28="Silverside"))>0,D5-60,D5)

Thank you so much! This is exactly what I needed - you're a star!

Is there any way that I can have it pick up anything with the word Silverside in it, as opposed to have to strictly just say 'Silverside'? For example, I have a product called 'Fat Off Silverside' but it won't be picked up by that formula as it's not exact?

Thank you again

 

[JamesCanale]

There sure is a way - using search and looking for a number result instead of an NA result:

MrExcelPlayground16.xlsx
	C	D	E	F	G	H
5		180
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23	120					Fred
24						George
25						Silly Silverside Goose
26						John Silverside
27						Mary
28						Sally
Sheet26

Cell Formulas
Range	Formula
C23	C23	=IF(--OR(ISNUMBER(SEARCH("Silverside",H23:H28)))>0,D5-60,D5)