How do I recieve the number of cells based off of three columns in Excel?

user_16448154 0
Viewed 46

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!

spreadsheet excel-formula excel
1 Answers

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!

user_122319840
Related