Hi
I am working on creating a metrics sheet for my department. I have a columns like project name, Task, Hours worked, Quantity of the task.
In the sheet which i have created i have 200 rows which is formatted with formulae.
MY requirement is that the project column should not be blank. Only if it is filled, I can take that as a valid entry for consolidation.
I have one more sheet where in I am consolidating the total hours worked. E.g
Name: Resource 1, Project: Blue, Task : QC, Hours : 2.
Name: Resource 1, Project: Red, Task: QC, Hours: 1.
I wanted to consolidate the hours worked by "Resource 1". A person need not enter a project name if his work is not billable. So to find out the billable hours i need to check if the Project column is filled. I have created a drop down list of projects and have created a in-cell drop down in each of the rows of 'Project". I need a formula which first checks the entered project is in the "projects" list, if so it should evaluate the SUM.
I tried using =IF((COUNTIF(Projects,Campbell_Nicole!D2)=1),SUMIFS(Campbell_Nicole!I2:I200,Campbell_Nicole!F2:F200,"*Nicol*",Campbell_Nicole!G2:G200,"Coding"),"")
But this doesnt work when a single column is left blank, it requires that all columns should be filled only then it computes the sum. Is there any other way to do this. Please suggest.
I am working on creating a metrics sheet for my department. I have a columns like project name, Task, Hours worked, Quantity of the task.
In the sheet which i have created i have 200 rows which is formatted with formulae.
MY requirement is that the project column should not be blank. Only if it is filled, I can take that as a valid entry for consolidation.
I have one more sheet where in I am consolidating the total hours worked. E.g
Name: Resource 1, Project: Blue, Task : QC, Hours : 2.
Name: Resource 1, Project: Red, Task: QC, Hours: 1.
I wanted to consolidate the hours worked by "Resource 1". A person need not enter a project name if his work is not billable. So to find out the billable hours i need to check if the Project column is filled. I have created a drop down list of projects and have created a in-cell drop down in each of the rows of 'Project". I need a formula which first checks the entered project is in the "projects" list, if so it should evaluate the SUM.
I tried using =IF((COUNTIF(Projects,Campbell_Nicole!D2)=1),SUMIFS(Campbell_Nicole!I2:I200,Campbell_Nicole!F2:F200,"*Nicol*",Campbell_Nicole!G2:G200,"Coding"),"")
But this doesnt work when a single column is left blank, it requires that all columns should be filled only then it computes the sum. Is there any other way to do this. Please suggest.
