Website Upgrade Incoming - we're working on a new look (and speed!) standby while we deliver the project

Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Excell function to capture data from a cell of group

8 replies [Last post]
Thu, 2008-03-27 09:03
shiv rao
User offline. Last seen 12 years 49 weeks ago. Offline
Joined: 10 Mar 2008
Posts: 62
Groups: Petro-Chem / Oil & Gas
Good morning to all,

I am trying to prepare an excell spread sheet for calculating % progress.

This Question is MS Excell related.

From a group of Columns [ i.e c5: L5]
to capture data from the cells c5 to L5 where ever the colour of the cell is green. and to return the cummulative value in the desired cell.

10,10,5,25,50,
if these are the values in the coloumn and only ist 2 cells are coloured green.
i am expecting the value to be returned is sum of the green coloured cells . i,e [ 10 + 10 } = 20
if the ist 3 cells are green . Then [ 10+10+5]=25
if all are green then [ 10+10+5+25+50] = 100.

Can any body help please

What function is to be used to call the colour of the cell?
If the Colour matches to the reqd Colour , how to call for the value?
And then Sum up the Values?


Regards

Shiv Rao
Top

Replies

Sat, 2008-03-29 01:27
#1
shiv rao
User offline. Last seen 12 years 49 weeks ago. Offline
Joined: 10 Mar 2008
Posts: 62
Groups: Petro-Chem / Oil & Gas
ANDREW , CAN I GET YOUR EMAIL ID .
SO THAT I CAN SEND YOU THE FILE .
RGERDA

SHIV
Top
Fri, 2008-03-28 09:12
#2
Andrew Podolny
User offline. Last seen 2 years 3 days ago. Offline
Joined: 16 Nov 2004
Posts: 130
Groups: Petro-Chem / Oil & Gas
Use custom function in VBA for this purpose. If you are not familiar with VBA just send to me excel file and I’ll do it. Regards,
Andrew
Top
Fri, 2008-03-28 05:25
#3
Oliver Melling
User offline. Last seen 5 years 41 weeks ago. Offline
Joined: 24 Apr 2007
Posts: 595
Groups: The GrapeVine
If you set the colour manually then you can’t do it. If you make the conditional format for example, so 1 makes the cell red, then you could do a SUMIF or COUNTIF with 1 as the criteria.
Top
Fri, 2008-03-28 02:04
#4
shiv rao
User offline. Last seen 12 years 49 weeks ago. Offline
Joined: 10 Mar 2008
Posts: 62
Groups: Petro-Chem / Oil & Gas

The Colour will be set manually .

and the value is to be calcualted based on the cell which are red in colour.

Shiv
Top
Fri, 2008-03-28 00:50
#5
Zhang Haixiang
User offline. Last seen 4 years 39 weeks ago. Offline
Joined: 14 Apr 2005
Posts: 250
Groups: None
it’s impossible to use cell format as criteria in function SUMIF.

how you set the color of a cell? mannually or by conditional format.

Top
Thu, 2008-03-27 11:21
#6
Oliver Melling
User offline. Last seen 5 years 41 weeks ago. Offline
Joined: 24 Apr 2007
Posts: 595
Groups: The GrapeVine
Click on a RED formatted cell and go ’Format’ ’Conditional Formatting’. The criteria will be there!
Top
Thu, 2008-03-27 11:18
#7
shiv rao
User offline. Last seen 12 years 49 weeks ago. Offline
Joined: 10 Mar 2008
Posts: 62
Groups: Petro-Chem / Oil & Gas
Oliver thanks for your good try .

We need the condition format as a criteria .

And thats not excepting.

Sumif[A2:A8]"criteria" range.

In the criteria we need to find if a cell is red in colour then we are going to sum the range of values.


But i think we are near to the answer.
Someone there to help us plz.

Regards

Shiv Rao
Top
Thu, 2008-03-27 10:26
#8
Oliver Melling
User offline. Last seen 5 years 41 weeks ago. Offline
Joined: 24 Apr 2007
Posts: 595
Groups: The GrapeVine
Conditional formatting uses a statement to decide if the cell should be RED. Look at the conditional format of a RED cell and use it as part of a SUMIF function.
Top