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!!

Find more posts tagged with

Accepted answers

Danielle Arteaga

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 cross-sheet), and this worked.

All comments

Jason Tarpinian

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.

Danielle Arteaga

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 cross-sheet), and this worked.

Joyce W

You are awesome! It worked! Thank you!!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.