Skip to content
ProjectControls.com
Oracle Primavera - PM6

Excell function to capture data from a cell of group

In the Open Standard
Shiv RaoMember for 18 years 7 months
Created
8
4
0
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
Save Follow Follow forum Report
Reply New topic

8 replies

Oldest first
  1. 0
    Oliver Melling ·
    #1
    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.
    Report
  2. 0
    Shiv Rao ·
    #2
    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
    Report
  3. 0
    Oliver Melling ·
    #3
    Click on a RED formatted cell and go ’Format’ ’Conditional Formatting’. The criteria will be there!
    Report
  4. 0
    Zhang Haixiang ·
    #4
    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.

    Report
  5. 0
    Shiv Rao ·
    #5

    The Colour will be set manually .

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

    Shiv
    Report
  6. 0
    Oliver Melling ·
    #6
    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.
    Report
  7. 0
    Andrew Podolny ·
    #7
    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
    Report
  8. 0
    Shiv Rao ·
    #8
    ANDREW , CAN I GET YOUR EMAIL ID .
    SO THAT I CAN SEND YOU THE FILE .
    RGERDA

    SHIV
    Report

Log in or join to reply

ProjectControls.com is free to join. Members can post questions, reply, vote and follow discussions.

Log inJoin free

Existing members: your original username and password still work. Forgot password?