Can someone help me get a Countifs formula to work from another sheet?

TSC

I'm trying to get a countifs formula to work from another sheet. I've clicked on select from another sheet and copied the formula that works on the existing sheet but still can't get it to work. Here is the formula:

=COUNTIFS({2022 Life Production}Rep:Rep, "Tim Crabtree", Status:Status, "Issued")

The name of the other sheet is 2022 Life Production and I want to count the times that rep "Tim Crabtree" has a product "issued". I got it to work on the existing page with the formula (see below) but can't get it to work when I try to bring this info from another sheet that I want to summarize the info.

=COUNTIFS(Rep:Rep, "Tim Crabtree", Status:Status, "Issued")

If anyone could help, I would really appreciate it

Thanks!!

Kelly Moore

Hey @TSC

We need to write this as a cross sheet reference. This reference might be helpful to you

For cross sheet references, you'll click the blue 'Reference Another Sheet' that is in the Formula Dialogue window. You will then select the other sheet. When the sheet pops up, you'll select the entire column (such as Rep column). As a good practice, notice how smartsheet calls the range 2022 Life Production Range 1. Rename this range as 2022 Life Production Rep . This will help you later in formulas. Once you have renamed the range, click the INSERT REFERENCE link. The reference is now inserted on your destination sheet. You will repeat this for every range you need in the countifs. The cross sheet references will always be inserted between automatically generated curly brackets.

You cannot just copy paste my formula, you must generate these cross sheet references yourself. Your range names may be different but the syntax of the formula will look like this

=COUNTIFS({2022 Life Production Rep}, @cell= "Tim Crabtree", {2022 Life Production Status}, @cell="Issued")

Does that work?

Kelly

TSC

Thanks @Kelly Moore . So far, I'm able to get it to work counting 1 Criteria Range but when I ask it to count a second I continue to get "Unparseable". I will continue to play with it to see if I can get it to work. Thanks for your help!!

Tim

Kelly Moore

Hey @TSC

A couple of things to trouble shoot.

1) Each separate cross sheet reference must be individually created. You cannot create a reference - let's call it reference#1, copy reference#1 as a new range and edit the name of the reference1 to reference2. This just changes the edited name of both the original and the pasted version to reference2.

2) If the first range-criteria pair is working, remove it (temporarily for trouble shooting purposes) from your equation and concentrate on making the 2nd range-criteria pair work. Once it works, add the 1 set back in.

Unparseable generally has to do with commas, parentheses and square and/or curly brackets. Make sure there are commas in between each range name and criteria. That is a frequent error I see. =Countifs(range1, criteria1, range2, criteria2, etc)

 If you post a screenshot I might be able to help spot a syntax error.

Kelly