Stumped with a simple formula - countif with sheet reference

I feel silly, but I'm stumped. I'm trying to do a countif with a sheet reference. I want to count how many rows in my Lease Tracker 2018 sheet have Agricultural in the Category column, but I keep getting #UNPARSEABLE. What am I doing wrong?

=COUNTIF({Lease Tracker 2018 Category}:{Lease Tracker 2018 Category},"Agricultural")

Any thoughts are GREATLY appreciated, thanks!

Screen Shot 2018-11-18 at 5.21.14 PM.png

Find more posts tagged with

Workspaces

Construction

Sheets

Formulas

Solution Center

Project Management

Cell linking

Comments

KO_in_Colo

I figured it out! I simplified plus I used a cell reference for the criteria.

=COUNTIF({Lease Tracker 2018 Category}, =Category1)

Andrée Starå

Hi,

Try this.

The range (Lease Tracker 2018 Range) is the whole column on the second sheet that has the Category reference in it.

Category is the column with the reference to what you want to count in the second sheets range. (You can change it to "Agricultural" if you only need that reference)

The formula should be in the Count column on the master sheet and if you have more references that you want to count you would fill the formula down, and because we have the @Row function, it will look at the Category column and what it should count from the second sheet.

=COUNTIF({Lease Tracker 2018 Range}; Category@row)

The same version but with the below changes for your and others convenience.

=COUNTIF({Lease Tracker 2018 Range}, Category@row)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Would that work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…