Ignore blanks using SUMPRODUCT - Multiple Criteria

[cbosch]

Good afternoon. I am working on an excel project using SUMPRODUCT to match multiple criteria from many others worksheets. Essentially, what I am trying to do is a project tracking database. The SUMPRODUCT function is doing this: Taking all of the projects worked on a specific date and counting the hours logged towards that project. Next it also references the tasks assigned through a pull-down and if they match with the master sheet then the count is given for the hours put towards that task of the project. Next I have it also looking for a sub-task and taking only the hours for that sub-task if the drop down is pulled. The problem I have is that if the sub-task is blank (there isn't one for that project) then it returns a blank. I want to see if it's possible to SKIP or IGNORE the criteria if it's blank. I hope I explained this right.

=SUMPRODUCT(--(Log_Projects=A13),--((Log_Hours)),--(((Log_Week='Tasks Per Week'!C$7))),--((((Log_Tasks=B13)))),--(((((Log_Sub_Tasks=C13))))))

The issue is the Log_Sub_Tasks... if this cell is blank, then it doesn't report any hours. What I would like is if that cell is blank for it to return the Log_Tasks and ignore that criteria.

 

[cbosch]

I also wanted to mention... if the first is possible, if it would also be possible if the Log_Tasks portion is blank to only show the projects. I know that this is better done in a pivot table and we have a pivot table... but once this is completed, we will also be matching this with a skill-assessment feature which will be really complex. Eventually we'll be porting this over to Access and then eventually into a web-driven standard... but the powers that be say it must be Excel

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(
    Log_Hours,
    --(Log_Projects=A13),
    --(Log_Week='Tasks Per Week'!C$7),
    --(Log_Tasks=IF(B13="",Log_Tasks,B13)),
    --(Log_Sub_Tasks=IF(C13="",Log_Sub_Tasks,C13)))

 

[cbosch]

Works perfect! HUGE THANK YOU!

 

[cbosch]

I just realized, that if all of the fields are blank (Projects, Tasks and Sub-Tasks) then the final number is equal to 1... is there a way to state if all of the variables are blank to show as blank?