Filter by value in pivot chart

[dianaCatz]

I have a list of 2000 names and I want the pivot table to show it grouped by industry and also then another one grouped by sub-industry, but the main thing is i want it to count how many have Sales growth below 0%. When I try to filter, every single company comes up and not just those with the negative growth. I made a sample file here - would want column L to be the count (in green) and either sorted by column Q or R. i would want to see the number of companies by column Q or R that have a negative value in L.

[Glenn Kennedy]

Is this what you wanted?

=LET(A,Sheet1!A3:T23,B,Sheet1!A1:T2,C,FILTER(A,INDEX(A,,12)<0),D,UNIQUE(INDEX(C,,17)),E,BYROW(D,LAMBDA(z,SUM(IF(INDEX(C,,17)=z,1,0)))),VSTACK({"Industry","Count <0"},HSTACK(D,E)))

[dianaCatz]

Yes, but its a bit of a hard formula. How do i move it to another worksheet, and how do I change columns if needed. if possible could you explain which pieces would be changed for column numbers and a new worksheet? If my worksheet is called RTY how does this change?

[Glenn Kennedy]

1. The formula IS in another worksheet and requires no further modification to put it in another sheet (???!!!) (apart from sub-formula A (Sheet1!A3:T23), which needs to be adjusted to suit your real data.

2. Subformula B is redundant and can be deleted B,Sheet1!A1:T2,

3. RED: 12 corresponds to the sheet column containing the % growth

4. BLUE. 17 is the sheet column corresponding to Industry

=LET(A,Sheet1!A3:T23,B,FILTER(A,INDEX(A,,12)<0),C,UNIQUE(INDEX(B,,17)),D,BYROW(C,LAMBDA(z,SUM(IF(INDEX(B,,17)=z,1,0)))),VSTACK({"Industry","Count <0"},HSTACK(C,D)))


amended file attached.

[dianaCatz]

this was so helpful! Thank you!

[Glenn Kennedy]

You're welcome. Thanks for letting us know that you got an answer.

---

Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.