Display text from data calculated using a Countif statement

[desert rat]

Hi Excel guru's

I have a question which I'm not even sure is possible relating to an Excel database I'm developing.

Some background info:
- I have used a variety of formulas to develop a summary table
- part of the summary table displays Tasks that are overdue

What I would like to do is display the text of those overdue tasks underneath the summary table.

I have uploaded a basic example of my Excel worksheet which hopefully might help. If possible I would like to keep the formulas the same as this Workbook will be constantly updated and also because there is a Macro running.

Any assistance would be awesome and greatly appreciated.

Thanks

 

[Hui]

A7: =IF(COUNTIF(Tasks!$C$2:$C$10,"<"&DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()+1)))<ROWS($E$7:E7),"",INDEX(Tasks!A:A,SMALL(IF(Tasks!$C$2:$C$10<DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()+1)),ROW(Tasks!$C$2:$C$10)),ROW(C1)))) Ctrl+Shift+Enter

B7: A7: =IF(COUNTIF(Tasks!$C$2:$C$10,"<"&DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()+1)))<ROWS($E$7:E7),"",INDEX(Tasks!B:B,SMALL(IF(Tasks!$C$2:$C$10<DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()+1)),ROW(Tasks!$C$2:$C$10)),ROW(C1)))) Ctrl+Shift+Enter

C7: A7: =IF(COUNTIF(Tasks!$C$2:$C$10,"<"&DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()+1)))<ROWS($E$7:E7),"",INDEX(Tasks!C:C,SMALL(IF(Tasks!$C$2:$C$10<DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()+1)),ROW(Tasks!$C$2:$C$10)),ROW(C1)))) Ctrl+Shift+Enter

Copy the cells down

Or see attached file:


To understand these have a read of: http://chandoo.org/wp/2011/11/18/formula-forensics-003/

 

[desert rat]

That's great Hui! Thanks so much for your help as I was able to modify your formula to line up with my existing database and it worked a treat

 

[Hui]

If you want to extend that to use multiple criteria
have a read of:
http://chandoo.org/wp/2014/11/10/formula-forensics-no-003b-lukes-reward-part-ii/
where I discuss the technique and believe it or not it is quite simple