Formula - Exclude Row if Column contains word "Complete"

[Bazola]

Hi All

I am trying to do the following and I'm struggling, I have a series of rows to track project steps, I need a sum of hours to be added up against each user assigned to task, but exclude the rows where the task Status is "Complete"

Row Headers below: -

Status ($F$6:$F$45)
Task Owner ($K$6:$K$45)
Actual Estimate ($M$6:$M$45)

The idea being, I choose who is allocated the task (Column K), fill in the estimated time for that task (Column M), and it then adds up the total effort for that individual, but I need it to exclude the row from the count when the "Status" (Column F) reads "Complete"

Below is where I plan to summarise each persons total effort, and the initial formula I used to search for the person and to the adding up.

=SUMIF($K$6:$K$45,"*Name of user assigned task*",$M$6:$M$45)

Hoping you can help,

Thanks
Bazola

 

[Alex Blakenburg]

You might want to update your profile to show what version of excel you are using.

Try this:

=SUMIFS($M$6:$M$45,$K$6:$K$45,"*Name of user assigned task*",$F$6:$F$45,"<>Complete")

 

[Bazola]

You might want to update your profile to show what version of excel you are using.

Try this:

=SUMIFS($M$6:$M$45,$K$6:$K$45,"*Name of user assigned task*",$F$6:$F$45,"<>Complete")

Hi Alex

Thanks so much for this, works a treat.

Kind regards
James

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help

 

[Bazola]

Thanks for letting me know. Glad I could help

Hi Alex

Quick question, I need to add Task Type functionality for the counts: -

Status ($F$6:$F$45)
Task Owner ($K$6:$K$45)
Actual Estimate ($M$6:$M$45)
Task Type (($I$6:$I$45) - NEW

Basically, when it does the original workings above which you provided, I need it to add up the value in column M as before for each task owner, but I need it to provide an output per Task Type. So if the name is Bill bloggs, and the task type is "Pre-Task", and there is a 4 in the Actual Estimate column (M), against that task type and person so provide the value just for that task type.

Sorry if this doesnt make sense!

 

[Bazola]

Hi Alex

Quick question, I need to add Task Type functionality for the counts: -

Status ($F$6:$F$45)
Task Owner ($K$6:$K$45)
Actual Estimate ($M$6:$M$45)
Task Type (($I$6:$I$45) - NEW

Basically, when it does the original workings above which you provided, I need it to add up the value in column M as before for each task owner, but I need it to provide an output per Task Type. So if the name is Bill bloggs, and the task type is "Pre-Task", and there is a 4 in the Actual Estimate column (M), against that task type and person so provide the value just for that task type.

Sorry if this doesnt make sense!

Hi Alex

I've managed to sort it

Thanks anyway

 

[Alex Blakenburg]

I am not sure I fully understand what you are after but to add more criteria you just follow the same pattern

=SUMIFS($M$6:$M$45,$K$6:$K$45,"*Name of user assigned task*",$F$6:$F$45,"<>Complete",$I$6:$I$45,"Pre-Task")

You can replace "Pre-Task" with a cell reference ie A1 (no quotes)

 

[Alex Blakenburg]

Glad to hear you got it sorted. I am login off for the night.