# help with if then

#### isacp

##### Board Regular
i have a sheet
a b c d
ph jp phone shoes 5
st jp phone shoes 7
ma jp phone shoes 4
ph sr store shoes 6
st sr store shoes 7
ma sr store shoes 8

column a tells me were there order was made
column b is the salesman name
column c tells me their dept.
d the amount sold.
(in reality the list has about 200 rows and 5 more columns of numbers

i need to have a code that tells it to look in column c and if the word phone is in the cell then to add the # in the row of "ma" to the row with "Ph"
(ph stands for phone, st = store, and ma=mail)
the idea is that a phone person might have worked in the store so those numbers are different. but the ma should be added to their normal position ie. phones or store.

this is very lenghty so please feel free to ask any q i need help
my boss wants this yesterday

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Zack Barresse

##### MrExcel MVP
Hi there,

Can you give a couple of real life examples? Can you post with the HTML Maker?

From what I understand, you are not looking for a formula, but to actually alter the data that you already have in your cells?

##### New Member
I'm trying to create a formula that solves a problem with database information.

I have a 3 columns, first 2 with job classifications and department classification and the third column with person's age.

I'm trying to create formulate whereby I can select 2 scenarios in the first 2 columns e.g. select all people who are mechnical and work in the CR & C mechanical depart, and then show the minimum and maximun ages of those 2 scenarios from the ages recordedin the 3rd column.

#### isacp

##### Board Regular
i cant post a sheet

#### Zack Barresse

##### MrExcel MVP

firefytr said:
Can you give a couple of real life examples?

..

From what I understand, you are not looking for a formula, but to actually alter the data that you already have in your cells?

##### New Member
I have a database with 3 columns, one with employees Dept, one with his job classification, one with his age. In another worksheet I have 2 drop down box where I can select a combination of employee dept and employee job classiffcations, e.g. "electrical dept" and "engineers". The drop down box choices are linked to a cell on the worksheet. In another cell I want to work out 1. the Minimum ages of engineers in Electrical dept and the maximum age of engineers in the electrical dept. I'm trying to create a formulat that looks at the results in the cells linked to the dropdown boxes and then gives me the min/max results from the ages of the relevant dept/job classification that are in the database. e.g. of the formula I have created is as follows
=MAX(IF(Database!\$E\$4:\$E\$128=\$S\$3, IF(Database!\$G\$4:\$G\$128=\$T\$3, Database!\$J\$4:\$J\$128, 0))).
In this formual I'm asking to look at S3 and T3 (which have the dropdown box selections), go the to database ranges, and choose the max ages for the S3/T3 choices. The maximun seelsm to be working but when I try and get a minimum age it keeps returning "0". Am I on the right track? Hope this makes sense to you and thanks you so much for your help

#### Zack Barresse

##### MrExcel MVP
Hi Sue,

Without seeing your data, I would assume ..

Max:
=MAX(IF((Database!\$E\$4:\$E\$128=\$S\$3)*(Database!\$G\$4:\$G\$128=\$T\$3),Database!\$J\$4:\$J\$128))

Min:
=MIN(IF((Database!\$E\$4:\$E\$128=\$S\$3)*(Database!\$G\$4:\$G\$128=\$T\$3),Database!\$J\$4:\$J\$128))

Of course, confirmed with Ctrl + Shift + Enter, as they're array formulas.

##### New Member
Thanks so much. It works great!

Replies
9
Views
417
Replies
1
Views
512
Replies
4
Views
732
Replies
65
Views
1K
Replies
6
Views
387

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,842
Messages
5,766,738
Members
425,376
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back