Formula to count projects that were active each month (COUNTIF/SUMPRODUCT)

I am setting up a project dashboard to track the status of projects over time.
I want to be able to know the current count of projects for each category (idea, active, complete, on hold, and canceled) as well as what these values were for previous months.
The logic I am using is tracking the dates that the status enters idea, active, and complete.
If the current month = the month the project became idea, active, or complete will add 1 to the count of projects that were ideas, active, or completed this month.
The tricky part is figuring out how many projects were active in a given month. I am attempting to use Countif to account for if the project is active and hasn't been completed, or if the completion date is past the current date.
The months and years are separated into 2 separate columns, so this requires 2 sets of COUNTIFs, one if the project is completed in the same year, and one if the project is completed in future years where the month value at the time of completion may be less than the month value at the time of being active.

• - if the current month is greater than or equal to the month that the project became active, count
  - if the current year is greater than or equal to the month that the project became active, count
  - if the month that the project was completed is greater than the current month, count
  - if the year that the project was completed is equal to the current year, count
  
  - if the current month is greater than or equal to the month that the project became active, count
  - if the current year is greater than or equal to the month that the project became active, count
  - if the month that the project was completed is less than or equal to the current month, count
  - if the year that the project was completed is greater than the current year, count
  
  - if the current month is greater than or equal to the month that the project became active, count
  - if the current year is greater than or equal to the month that the project became active, count
  - if the project has not yet been completed, count

I am using a Power Query to pull the data from sharepoint ("WB" tab). In my dashboard, I don't want to look at projects that track the project type of "Capital ". So the formula also needs to include a note about that.

Please Login or Register  to view this content.

To simplify I have also tried to use last months active projects + this months active projects - completed - on hold - canceled but that also hasn't provided me with any luck.
If anyone has a solution or ideas it would be greatly appreciated!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a description of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Hi Ali,

I forgot to hit upload - thanks!
The desired results for E23 and I23 in the Raw Data tab are 23 and 26, respectively. I'v eincluded these values in the row below.
I want all of the values highlighted in yellow to be correct, but the values for august are the ones I can quick check against.

It seems to me as if the rational for E23 goes something like this: IF the project is an idea and IF the project has not yet become active (meaning date later than today OR blank) and IF the project type is not 'Capital' (ran 'Find&Replace' for the 'Project type' column to get rid of the space in 'Capital ') then count it.
I used the following formula to apply this rational:

Formula:

Please Login or Register  to view this content.

The formula yields 28 as opposed to 23, however when I applied filters to the data on the WB sheet (as shown in attached copy of file) that would correspond to the logic I found that one of the projects had been canceled and four are on hold, which makes me wonder if excluding 'On hold' and 'Canceled' projects should be considered.
Let us know if you have any questions.

Here's an attempt at a partial solution involving only column E on the Raw Data sheet.
Two helper columns are added to the WB sheet, populated respectively by the formulas:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

The formula that populates column E on the Raw Data sheet is:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.