Add and display the number of numbers In Excel 2013, I want to make a display sh

Question

Add and display the number of numbers

In Excel 2013, I want to make a display showing the number of tasks complete compared to the total number of tasks.

To declare a task complete, the date is entered in the "Actual End Date" column.

In the example below, there are 3 top level tasks and total of 6 sub-tasks.

I therefore need to display the number of top level numbers (in the multi-level numbering system), in cell D2.

Then I need to display the number of top level completed tasks in B2.

In the example below the display cell D2 should have "3", and B2 should have "1".

I have tried using "LEN", "SUMIF", "COUNT" like this =COUNTIF(A11:A53,LEN(A11:A53)<=2) but can't get it to work.

It either shows "0" or sums all the numbers.

I need help with the formula to make it work.

thanks!!!

Explanation / Answer

For D, try this formula.

=SUMPRODUCT(--(ISERROR(FIND(".",$A$2:$A$101))=TRUE)*--(A2:A101<>""))

The orignial formula

=SUMPRODUCT(--(ISERROR(FIND(".",$A$2:$A$100))=TRUE))

considers empty cells also as a top level task.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.