help with if then

isacp

Board Regular
Joined
Dec 16, 2004
Messages
135
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 can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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?
 

sue.Caradonna

New Member
Joined
Jan 17, 2005
Messages
8
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.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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?
 

sue.Caradonna

New Member
Joined
Jan 17, 2005
Messages
8
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
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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.
 

Forum statistics

Threads
1,147,508
Messages
5,741,570
Members
423,668
Latest member
Audorin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top