Excel formula help please

Mark M

Mark M

Mark M

Soldato
Joined
6 Jan 2006
Posts
3,413
Location
Newcastle upon Tyne
I have 6 months of data - Aug to Feb and what I want to identify is the rows that have a figure in Aug but nothing in the more recent months.

To give an example

Code: 
            Aug   Sep    Oct    Nov   Dec    Jan   Feb
A          100      0       0     0      0     0     0
B          150      0       0     0      0   100     0
C            0    100       0     0      0     0    50

What I need is a way to highlight A and not B or C. My head is going to explode Ive tried all sorts of nested IF's and &'s but to no avail!!

Hopefully someone can come up with something!!!

Thanks
 
Tried that and just a basic sort but you can only do upto 3 options and I need 6, possibly more in the future.
 
A bit clumsy, but how about an extra hidden column that does =IF(Augcell>0, IF(SUM(Sepcell:Febcell)=0, TRUE,FALSE), FALSE)
Then conditional formatting on the row based on the boolean value in the cell for that row?
 
Not fussed about it being clumsy at this stage, as wonko says it can easily be hidden!!

Many thanks guys, my brain can breath a sigh of relief now!!
 
Hi Mark,

Going by your request, you will be able to do this via conditional formatting.

Highlight all of your data rows and choose Conditional Formatting and set Condition 1 as follows :

Formula Is =IF($A2=SUM($A2:F2),TRUE,FALSE)

(Assuming your August data is in Column A).

Then just apply what ever specific format you want. E.g. Highlight the rows in Yellow.

The formula is just saying if the value in column A is the same as the sum of the row, then the row will highligh yellow which is what you are looking for?
 
You must log in or register to reply here.
Back
Top Bottom