Help with a formula

Hi,
Could someone help with a formula please? I am using Excel 2016.
I am trying to get the count into Column B of those items which are still open within month (“In Progress” in Column G in Sample Data).
I’ve tried Countifs function, but don’t seem to get this to work.
As you can see on the Report Tab, I need to count the number of reports opened in month, Completed in month and what is still open within month if its not completed.
I have attached a sample for use.

Thanks in advance

MMB

Added 2 more columns: In Progress and Completed within 30 Days

Open Last of Month: =COUNTIFS('Sample Data'!$A:$A,""<""&B$1,'Sample Data'!$E:$E,"">=""&B$1)
+COUNTIFS('Sample Data'!$A:$A,""<""&B$1,'Sample Data'!$E:$E,""="")"

In Progress: =COUNTIFS('Sample Data'!$A:$A,"<="&EOMONTH(B$1,0),'Sample Data'!$E:$E,">"&EOMONTH(B$1,0))+COUNTIFS('Sample Data'!$A:$A,"<="&EOMONTH(B$1,0),'Sample Data'!$E:$E,"=")

Completed within 30 Days: =COUNTIFS('Sample Data'!$E:$E,">="&B$1,'Sample Data'!$E:$E,"<="&EOMONTH(B$1,0),'Sample Data'!$F:$F,"<=30")
% Completed within 30 Days: =E4/(A4+B4)

Hi,

I need to show what was opened in the month of in this case April, What was completed/closed in April and what is still open in April, which will then need to be added to the Opening numbers for May.

If its still open at the end of April, then it should in Column G under request Status show "In Progress" Which i thought might be easy to count. Maybe this is wrong and i need something different to show this.

I then also need to detail how many were completed within the 30 days target, even if that date goes beyond April and into May, so i need a countifs formula i think .

I have built the sample so that i could see if there were any issues over the 4 month period of trying to get this data to work, and give me the correct figures i need. for when i build the proper work sheet and reports i need.

So any advice or help is welcomed.

I Have simplified what i require, new sample attached, with questions for each months required information. I need to put the right formulas into those cells to give me the numbers for an overall monthly, quarterly and yearly report.
As always thanks in advance for helping.

Did you see post #5?

Originally Posted by josephteh

Did you see post #5?

Hi.

Yes however it doesn’t pick the information that I wish to report.
As part of the April nos there is 1 in progress
But that’s not pulling through.
Also. The number completed should show only those in April. Those with a complete date in May should show in May only.
If a report is opened in April but then closed in May, I need to show that it was included in mays Open figures too. As it might have been closed later in May.
I thought this would have been easy for me, but I’m finding it difficult.

Please provide your expected results.

Josephteh,

Having had a relook at this with a rest and fresh set of eyes, thank you so much as this is what I do require in terms of the formula needed to capture what I asked for.

You are welcome! Thanks for your feedback and Rep and for marking this thread "Solved"!