If statement

**SpikeyUK** · 10-02-2007, 08:22 AM

Hello i need some help with some code that i am putting into a spredsheet, the spreed sheet lists computer equipment.

i have a collum that show if it has been installed

=IF(I15, yes,(H7 = H7+1))

I has a value of Yes when it has been installed and i need it to update H7 to show the number of items that have been installed but i can not get it to work is it possible to get this to work

thxs for the help

**mudraker** · 10-02-2007, 08:27 AM

Welcome to excel forum

Please read forum rules (rule 1) & use suitable thread titles

A thread with the rules is available at the top of each forum or see link below for rules

**oldchippy** · 10-02-2007, 08:27 AM

Is this what you mean?

=IF(I15="yes",H7+1,H7)

By the way please read the rule on posting and change the title of the question - thanks

http://www.excelforum.com/showthread.php?t=613917

**SpikeyUK** · 10-02-2007, 08:37 AM

thxs for the help sorry i read the rules just after i posted when i try and use the code i keep getting a error. thank you i have got it working now. is there a way to get it to work for all of H so when more items get added it will auto upadte the list

thxs for the help

**SpikeyUK** · 10-03-2007, 03:51 AM

i am still having a few problems with this in collom H7 i need it to show how many time Yes is showen to show how many items have been installed, but i cant get it to work.

=IF(I15="yes",I7+1,I7)

this was the only way i could get it to work to count one of the items H7 is where the end value is displayed in the table.

=IF(I15:300="yes",I7+1,I7)

when i tryed this it did not work i think i need sum insted of IF for this to work

thxs for the help mike

**oldchippy** · 10-03-2007, 06:13 AM

I'm trying to understand exactly what you need, but is it this in H7?

=IF(COUNTIF(I15:I30,"Yes")>0,I7+COUNTIF(I15:I30,"Yes"),I7)

I15:I30 being the range where you want to count how many "Yes" 's and I7 is the quantity without the number of "Yes" 's added?

**SpikeyUK** · 10-03-2007, 08:43 AM

thank you for your help i havenow got that part of it working so i am happy thxs :-)

**SpikeyUK** · 10-03-2007, 01:08 PM

I am now trying to add a few more parts to the spreedsheat but i dont know if it is posible to do them.

as before in Colum I i have the status of the item they are Pending,Installed, Pending Install, Lost.

Is there a way to get a row with all the computer info in it to change colour to say Green when Installed is entered into Colum I

thxs for the help

**oldchippy** · 10-03-2007, 01:38 PM

Yes it is possible with Conditional Formating, but could you attach a zipped copy of you file, with exactly what you need and I'll have a look for you?

To post a zipped example, go to “My Documents” or wherever you have your file stored, right-click and “Send to > Compressed file”, then attach this zipped file to your post.

**SpikeyUK** · 10-03-2007, 02:46 PM

here is the file, thxs for the help

**oldchippy** · 10-03-2007, 03:21 PM

I'm using Excel 2003, but basically you need to go into Conditional formating and set it up like so.

http://www.contextures.com/xlCondFormat01.html

**SpikeyUK** · 10-03-2007, 03:26 PM

Originally Posted by oldchippy

I'm using Excel 2003, but basically you need to go into Conditional formating and set it up like so.

http://www.contextures.com/xlCondFormat01.html

not a problem i can save it in the older format

i stared to add the diffrent contextures but it wont let me add more then 3, any idears

thxs i have a read up on it as well

**oldchippy** · 10-03-2007, 03:45 PM

You can set more than 3 conditional formats with 2007

**SpikeyUK** · 10-04-2007, 03:12 AM

Originally Posted by oldchippy

You can set more than 3 conditional formats with 2007

ok thank you for the help, my olny problm is that i have 2007 but most people that will need to see the spreedsheet are on 2003 Conditional formating the only way of doing. Its not a big problem, the main thing i would like to do with the conditional formats and that is to use the date from when the stock came in and compare that to the date Actual date and say i it have been there for over 6 week then change the colour on it that way. its just a shame you can only have 3 conditional formats in a cell