Using countif and counta together

[simoneasy]

I am having difficulties in finding the right formula to return a number of completed tasks. I am working on 2 sheets, the first is an overview sheet giving a headline of outstanding and completed tasks for individual projects. The second sheet is a full task list for each project.

I want to get a number for how many tasks have been completed for a particular project. I have successfully created the formula for getting the count on the individual projects, however, I am struggling to get a formula that says 'if the project name in column A:A is "x" and there is a date in column B:B, then count it and return the total of dates counted into this cell.

I have included a couple of tables below to help explain a little more. The top one is the overview sheet and the bottom one is the individual task sheet. I haven't included all columns on the sheet, but only the ones which I want to get the data from.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Project Name[/TD]
[TD]Total Tasks[/TD]
[TD]Completed Tasks[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]=countif(sheetname!$A:$A, A1)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]Project Name[/TD]
[TD]Completed Date[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]31/01/18[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]31/01/18[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]31/01/18[/TD]
[/TR]
</tbody>[/TABLE]

In essence, I would need the overview sheet to show 2 completed tasks for Project 1 and 1 completed task for Project 2.

I hope I have explained that well enough for you to understand, but please feel free to ask any questions.

Thank you very much.

Simon

 

[Gerald Higgins]

Hi, welcome to the board, this sounds like a case for the COUNTIFS function.

 

[simoneasy]

Thanks Gerald, is there any way that you can expand on that for me please?

 

[Gerald Higgins]

In principle, something like this . . .

=COUNTIFS(A:A,"X",B:B,">0")

You might need to tweak the details

 

[simoneasy]

Thanks Gerald, I have tried it and at least I am not getting an error this time, although it only returns a value of 1, where there is 2 cells filled with data. Do you know if I can upload the excel file here so you can see the document?

 

[simoneasy]

Ignore me Gerald, I have managed to make it work now.

Thank you for your assistance, really appreciate it!