Count based on Week and Year for cells having Hyphens "-"

0
votes

I have a tasks tracking sheet and want to count number of tasks received based on week number for a particular year. Thanks for the post COUNTIF date occurs in same week number and year

But still i have problem that i have dates in a column where many of the cells are non-empty and filled with Hyphens "-". If i delete Hyphens, formula working fine. But i need to keep "-" in cells where date not entered.

Initially i tried 'Sumproduct' funtion, it shows #Value error because of Hyphen in cells. And i added the code --(Task_Status!$X$7:$X$250<>"-"). But still it showing same error. In my formula, where Q2 is weeknumber 1 to 58 in a rage of cells Q2 to BQ2, that i have to copy(drag) the formula from Q3 to BQ3. And $C$3 is cell having 4 digit Year (E.g. 2019). Please help... A image here-->Received&Completed_CW 

=SUMPRODUCT(--(ISOWEEKNUM(Task_Status!$X$7:$X$250)=Q2),--(YEAR(Task_Status!$X$7:$X$250)=$C$3),--(Task_Status!$X$7:$X$250<>"-"))
1
excel-formulacountifweek-number

1 Answers

0
votes

Perfect i found a temporary formula... Its working fine if non-hyphen "-" exist until end of cell range specified by INDIRECT function...

=SUMPRODUCT(--(ISOWEEKNUM(INDIRECT("Task_Status!$X$7:$X"&COUNTIFS(Task_Status!$X$7:$X$250,"<>-")+6,TRUE))=Q2),--(YEAR(INDIRECT("Task_Status!$X$7:$X"&COUNTIFS(Task_Status!$X$7:$X$250,"<>-")+6,TRUE))=$C$3))

Please give a better solution than this if anyone found... Because still Hyphen present between start cell X7 and end of cell range specified by INDIRECT function give no result... still need help...

Thank you for all who tried for a solution... and thanks to post Excel Define a range based on a cell value and Using the value in a cell as a cell reference in a formula?