CountiF - within particular date range

[Niranjanrajrishi]

Hi,

I am trying to prepare a report of work items for each day of the month. I made the inflow, outflow with countif formula. I am stuck with the pending items on each day. My criteria is if there are any items with Open status (Column D in the Data sheet) and Column E (Data sheet) is blank and it should consider only if the items (date is in the column AD in the Data sheet) fall under till yesterday (workday). Can you help me t find an excel formula to find the pending cases?

 

[bosco_yip]

Hi,

1] Your "Data" sheet, Column AD "Open_date" formula returned value are not true date, and the formula AD2 should be changed to :

=IF(F2="","",INT(F2))

>> custom format the cell with : dd-mm-yyyy >> and copied down

2] Then, in "Report" sheet C18, formula copied across :

=IF(C$2<=WORKDAY(TODAY(),-1),COUNTIFS(Data!$D:$D,"OPEN",Data!$E:$E,"",Data!$AD:$AD,C$2),"")

Regards
Bosco

 

[Niranjanrajrishi]

Thank you so much!