2

I'm not too sure how to word this problem so, I apologize for the vagueness. Here is what I am trying to do though:

I have a large Excel table with a ton of values, I however, only care about 3 columns. The three columns I have are "Project Name", "Active/Planned", and "Week of Month". Here is an example of some values I would have:

Project Name Active/Planned Week of Month
StoreProj Active 2021-07 Jul-Wk1
SecProj Planned 2021-07 Jul-Wk2
StoreProj Active 2021-07 Jul-Wk1

Now, I have used a formula to get the number of projects based on a specific week month and avoiding duplicate values for the project name. The code I used returns an integer of the number of projects. Here is what I used:

=IFERROR(ROWS(UNIQUE(FILTER(Table[Project Name],Table[Week of Month]=2021-07 Jul-Wk1))), 0)

This works as intended. Now the issue I am running into is that I need to filter through these rows as I did previously, but now I need to include the "Active/Planned" column. So, I want to be able to see how many projects I have based off of the week of the month and return a number of projects (excluding duplicate names), but be able to filter through that integer output based off of the active/planned projects. So in a perfect scenario I can choose the week of month and if the project is "Active" or "Planned" and see the amount of projects I have.

This might be an easy fix so I apologize, I am just stumped, any help would be greatly appreciated. Thanks!

pastacosta
  • 33
  • 5

1 Answers1

1

Work through that step by step, you've got the FILTER function which is giving data to the UNIQUE function, to the ROWS function, and then your IFERROR. However, the data about whether each line/row is 'Active' or 'planned' isn't passed out beyond the FILTER function, so can't be used by anything further on in the above sequence.

Boring theoretical advice out the way, try this;

=COUNT(IF(UNIQUE(FILTER( Table[[Project Name]:[Active/Planned]], Table[Week of Month] = "2021-07 Jul-wk1"))= "Active", 1))

Explanation:

  • FILTER(...) outputs records with the relevant date filter, however it outputs Table[[Project Name]:[Active/Planned]] - both columns, to ensure all relevant data is there.
  • UNIQUE(...) Then narrows that down to unique values, although by this stage I'm not 100% sure you need this.
  • IF(... = "Active", 1) then replaces the 'Active' outputs with 1s
  • COUNT() returns the number of cells in the above that contain a number (the 1s from the IF())

Yes, you can't use COUNTIF on arrays (and all except that last bullet point above are outputting arrays not single values) - and no, I didn't know that before attempting to answer this question, found it over at a different question!

Spencer Barnes
  • 2,809
  • 1
  • 7
  • 26
  • Thanks that is very helpful! I do have a follow up question though, there are multiple columns between "Project Name" and "Active/Planned". Is there a way to only grab these two data ranges instead of also including everything in between? – pastacosta Jul 14 '21 at 15:18
  • I don't know straight off the top of my head sorry, but as long as none of the other columns contain only the word 'active' or 'planned' I don't think it would affect the end result either way? – Spencer Barnes Jul 14 '21 at 15:20
  • You're correct it doesn't affect the values. Thank you so much for your work! – pastacosta Jul 14 '21 at 15:25
  • 1
    @pastacosta .. [If you want to say "thank you," vote on or accept that person's answer](https://stackoverflow.com/help/someone-answers#:~:text=%20To%20accept%20an%20answer%3A%20%201%20Choose,un-accept%20the%20answer%2C%20at%20any%20time.%20More%20) – Naresh Jul 14 '21 at 15:53
  • Great, glad you got it working! would you mind marking this as the answer to the question so it isn't left marked as unanswered on the site? – Spencer Barnes Jul 14 '21 at 15:54