Formula to count number of items if it matches both headings.
Hello,
I am currently stuck on this formula. I'm not sure what I'm doing wrong but the formula keeps on providing a return of "1" for everything, when I know I have "2" counts of items which are under category 1 and have a status of in progress.
=COUNT(COLLECT({Status}, $Label@row, {Project Category}, [Category 1]$1))
Any advice would be appreciated! Thank you!!
Best Answer

Not sure you need "COLLECT" here (or the fixed reference to the Label column).
Try this instead: =COUNTIFS({Status}, Label@row, {Project Category}, [Category 1]$1)
I created some sample data (including a crosssheet), and this worked.
Answers

Try updating your formula to
=COUNT(COLLECT({Status}, {Status}, $Label@row, {Project Category},[Category 1]$1))
Your COLLECT() function should begin with 2 ranges, one to specify the range you want to collect (then count), then the first range to want to use as criteria. I'm surprised it's returning 1, from what I see you should be return #UNPARSEABLE error with that. Let me know if adding the addition {Status} range fixes it!
You could also use a COUNTIFS() function if the COLLECT() keeps giving you problems.
Jason Tarpinian  Sevan Technology
Smartsheet Aligned Partner

Not sure you need "COLLECT" here (or the fixed reference to the Label column).
Try this instead: =COUNTIFS({Status}, Label@row, {Project Category}, [Category 1]$1)
I created some sample data (including a crosssheet), and this worked.

You are awesome! It worked! Thank you!!
Help Article Resources
Categories
Check out the Formula Handbook template!