I am trying to count how many items from one table meet a variety of conditions and my formula works until I add the last which is trying to use the values in one column from a different table as the criteria in another table. I believe the issue is that it is pulling the criteria as a whole and not looking at each row as an independent value. I was hoping to not have to create an intermediary table and do all this in one formula. Please let me know the best way forward.
Formula works this way:
COUNTIFS(Features[T-Shirt Estimate], "X-Small", Features[Portfolio Ask], ">=" & Calculations!$B$7, Features[Portfolio Ask], "<=" & Calculations!$C$7)
Does not work this way:
COUNTIFS(Features[T-Shirt Estimate], "X-Small", Features[Portfolio Ask], ">=" & Calculations!$B$7, Features[Portfolio Ask], "<=" & Calculations!$C$7, Features[Parent Capability], Epic1[Title])
I have tried taking out the Epic1[Title] and replace with an individual value and it works which is why I don't think it is looking at each value independently.
I have included a document. The second sheet has the two tables showing the difference. Please let me know if you have any questions.
Hi Ross,
Welcome to our forum! Thanks for sharing your file. It makes the question easy to understand and answer.
There are a couple of issues:
1. The criteria range 'Features[Parent Capability]' contains parent numbers, but the column containing the criteria, 'Epic1[Title]' doesn't contain the same data therefore it will always return zero as the result because it can't find any parent capabilities that match the Epic1[Title]. I think you meant to reference 'Features[Title]'.
2. When an Excel Table structured reference uses the @ operator e.g. Epic1[@Title] it is using implicit intersection to determine which row in the Title column to reference. Implicit intersection means it uses the row the formula is on. Taking the formula in cell G17, this Epic1[@Title] is referencing cell C17 and your table doesn't extend that far. More on Structured References here.
So, even if you change the reference to 'Features[Parent Capability]' to 'Features[Title]' it won't return a match because it's looking in cell C17.
You need to add a column to the Features table that classifies each Title into a category that says it's either in the list in the Epic1 table or it's not. You can then use that criteria in your formula.
See attached - second table on Parent 1 sheet.
Mynda
It looks to me as though you can do it without altering the table structures by using:
=SUMPRODUCT(COUNTIFS(Features[T-Shirt Estimate], G$16, Features[Portfolio Ask], ">=" & Calculations!$B7, Features[Portfolio Ask], "<=" & Calculations!$C7, Features[Title], Epic1[Title]))
in G17 on the Parent 1 tab, then copy across and down as required. If you need to match to Parent 1 in the Features table as well, you'd have to add that as an additional criterion (and you'd have no matches on that tab, as Mynda said).
