Cross-Sheet Formulas in Sheet Summary

Megan Harry

Hello,

I am trying to do a cross sheet formula in one of my sheet summaries. I have done it when it only affects one column, but in the formula I am trying to do I need to include two formulas. Is it possible to select two columns from my sheet when cross referencing in sheet summary? I have copied my formula thus far. I am stuck on how to get the cross sheet information in there.

=COUNTIFS([Validation Type]:[Validation Type], OR(@cell = "Blue Badge"), Date:Date, AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 3, 31))) +COUNTIFS(

The second part of the formula should almost mirror the first part, but it will be pulled from a different sheet. I will need the Date column and the Validation Type column from my other sheet. Is this possible to do?

I would appreciate any insight! Thank you!

Mike Wilday

Yes, you should be able to pull from two different cross sheet ranges. When prepping to choose your ranges select the "Reference another sheet" link in the help box when designing your formula.

https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

Megan Harry

I see how I can select multiple columns now, but I still can't my formula to work. This is what I am trying to pull. When I select multiple columns it pulls the first column I choose, last column I choose, and everything in-between. Do I need to pull the columns individually to make my formula work? Any advice?

=COUNTIFS({2020 Q4 Validations Range 1}, [Validation Type]:[Validation Type], OR(@cell = "Amazon"), Date:Date, AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 3, 31)))

I would love some help! Thank you!

Mike Wilday

Yes, pull each column individually.

Megan Harry

@Mike Wilday

I am trying to do it this way:

=COUNTIFS({2020 Q4 Validations Range 2}, [Validation Type]:[Validation Type], OR(@cell = "Amazon"), {2020 Q4 Validations Range 3}, Date:Date, AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 3, 31)))

I am getting #INVALID OPERATION. Any feedback?

Thank you.