Hi

Thanks for reading my post, i need some help with a tricky formula.

Currently I have a dashboard sheet that reads and calculates vales from another sheet (2 columns), this sheet is fed from 2 data sheets to consolidate the data, i want to do away with the middle sheet and make the calculations direct from the data sheets into the dashboard.

The current formula is: =COUNTIFS(Transition!D$4:D$481, "Discovery",Transition!C$4:C$481, "<>Discovery") this gives me a total of 14 which means 14 jobs have left Discovery status that were at discovery last week.

What i am trying to do is this:

Same cell as above contains: =SUMPRODUCT(COUNTIFS(INDIRECT("'"&$D$4&"'!$B$2:$B$481"),"=Discovery", INDIRECT("'"&$C$4&"'!$B$2:$B$481"),"<>Discovery"))
Cell D4 contains: 10.10.14
Cell C4 contains: 15.10.14
These 2 values are the names of Sheets in the workbook

Any suggestions would be really appreciated or am i trying to do too much in 1 cell?

Thanks