COUNTIFS Formula help in sheet summary

Madeline

Hi,

I currently have one field in my sheet summary which calculates the current project week.

I also have one which calculates the number of tasks in the current week with the below formula, linking a column to my summary field showing the week number tasks are completed:

=COUNTIFS([Projected Week Work Complete]89:[Projected Week Work Complete]932, =Week#)

I want to add another criteria to the above, based on one column in my sheet, which shows a due date for tasks. I want to formula to count the tasks projected to complete that matches the current week in my sheet summary (already working), but also add the criteria that the due date column isn't blank.

I tried this formula but get incorrect argument set:

=COUNTIFS([Projected Week Work Complete]89:[Projected Week Work Complete]932, =Week#, [Due on]77:[Due on]932, <>"")

Is the error due to the date format in my due column or something else?

sharkasits

@Madeline my guess is it's because your ranges aren't the same size. Is there a reason Project week starts on line 89 while Due on starts on 77?

Ipshita

@Madeline I concur with what @sharkasits mentions. Also, what is your formula for the Week in the summary field? Usually for a COUNTIFS formula, you don't have to select rows, just taking the @row should get it going for you. I have created a test scenario for you but I need you to tell me what you are inserting in the Week summary filed formula for me to test it out further -

Cheers,

Ipshita

Madeline

Hi Both,

Thanks so much for your help, as @sharkasits said, the problem was the size of my ranges. It's now working as I wanted it to :)

Thanks,

Madeline