# Countifs using cross sheet references

Options
✭✭✭✭✭

Hello,

I am trying to get a count of items with these Criteria

Stream = XXX

Window Start <>""

Survey Status = ""

Assignment Status =

Vendor Assigned

Vendor Scheduling

Late - requested update

This is the current formula I have which is populating a quantity of 0 (Actual count should be 325.

=COUNTIFS({Site Survey Stream}, \$STREAM\$1, {Window Start}, <>"", {Survey Status}, "", {Assignment Status}, "Vendor Assigned", {Assignment Status}, "Vendor Scheduling", {Assignment Status}, "Late - requested update")

Any help certainly would be very appreciated.

Sharon C.

• ✭✭✭✭✭✭
Options

Make sure your formula is mimicking your filter. Your current formula currently states the the {Survey Review Date} must be greater than or equal to 6 Sept. AND 12 Sept. at the same time. I imagine one of those is probably supposed to be a less than.

• ✭✭✭✭✭
Options

One more Question:

Once again Mr. Newcome, you are Johnny on the Spot. Thank you so much. I have another issue:

Have the following formula:

=COUNTIFS({Site Survey Stream}, \$STREAM\$14, {Survey Status}, "Green", {Survey Reviewed Date}, >=(DATE(2020, 9, 6)), {Survey Reviewed Date}, >=(DATE(2020, 9, 12)))

Coming up with a sum of 0, but when I apply the filter on the source sheet it is showing a quantity of 7, what am I doing wrong?

Thank you

• ✭✭✭✭✭
Options

Oh and the total was correct without drilling down to the date range.

• ✭✭✭✭✭✭
Options

Make sure your formula is mimicking your filter. Your current formula currently states the the {Survey Review Date} must be greater than or equal to 6 Sept. AND 12 Sept. at the same time. I imagine one of those is probably supposed to be a less than.

• ✭✭✭✭✭
Options

Thank you so much. I was looking at that formula way too long and couldn't see the obvious. Fresh eyes make all the difference

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!