# Trying to use a formula that needs data from 3 serrate sheets

✭✭✭✭✭

I have used an Index formula to create 3 separate sheets containing various data. I had to do this as each sheet would have contained more than 100,000 cells so have to create in 3 sheets.

I have used the following formula that calculates details from one of the sheets...Name of this source sheet is "Country Reference Sheet - Countries 1 " and this works fine. Any idea how I can extend the formula to look for the same data contained in 2 separate sheets. These sheets would be "Country Reference Sheet - Countries 2" and "Country Reference Sheet - Countries 3"

=COUNTIFS({Country Reference Sheet - Countries 1 Range 2}, FIND("aYes", @cell) > 0, {Country Reference Sheet - Countries 1 Range 1}, "ACTION", {Country Reference Sheet - Countries 1 Range 3}, >=DATE(2022, 1, 1), {Country Reference Sheet - Countries 1 Range 3}, <=DATE(2022, 3, 31)) 2}, >=DATE(2023, 1, 1), {Country Reference Sheet - Countries 2 Range 2}, <=DATE(2023, 12, 31))

Any help much appreciated.

• ✭✭✭✭✭✭

Hey @Mike Thorpe

You would need to develop separate CountIFS and add them together. You can do this is one formula =COUNTIFS(all sheet 1 criteria)+ COUNTIFS(all sheet 2 criteria). Sorry but you can't combine it within one big COUNTIFS

Could you use a Summarized Report to pull the data for you?

Kelly

• ✭✭✭✭✭

Thanks Kelly that is great, will try that. Unfortunately the report option doesn't work for the end result.

Kind regards

Mike

• ✭✭✭✭✭

Hi Kelly

Unfortunately that didnt work, came up with an Unparseable Error message.

Formula I sued was as follows:

=COUNTIFS({Country Reference Sheet - Countries 1 Range 2}, FIND("aYes", @cell) > 0, {Country Reference Sheet - Countries 1 Range 1}, "ACTION", {Country Reference Sheet - Countries 1 Range 3}, >=DATE(2022, 1, 1), {Country Reference Sheet - Countries 1 Range 3}, <=DATE(2022, 3, 31) + COUNTIFS({Country Reference Sheet}, FIND("aYes"@cell) >0, {Country Reference Sheet - Countries 2 Range 2}, "ACTION", {Country Reference Sheet - Countries 2 Range 3},>=DATE(2022,1,1),{Country Reference Sheet - Countries 2 Range 3},<=DATE(2022.3.31)))

The second part is as follows:

+ COUNTIFS({Country Reference Sheet}, FIND("aYes"@cell) >0, {Country Reference Sheet - Countries 2 Range 2}, "ACTION", {Country Reference Sheet - Countries 2 Range 3},>=DATE(2022,1,1),{Country Reference Sheet - Countries 2 Range 3},<=DATE(2022.3.31)))

Did I need to add closing parenthesis after the first one?

Regards

Mike

• ✭✭✭✭✭✭

Yes, each COUNTIFS needs to be closed off

=COUNTIFS({Country Reference Sheet - Countries 1 Range 2}, FIND("aYes", @cell) > 0, {Country Reference Sheet - Countries 1 Range 1}, "ACTION", {Country Reference Sheet - Countries 1 Range 3}, @cell>=DATE(2022, 1, 1), {Country Reference Sheet - Countries 1 Range 3}, @cell<=DATE(2022, 3, 31)) + COUNTIFS({Country Reference Sheet}, FIND("aYes"@cell) >0, {Country Reference Sheet - Countries 2 Range 2}, "ACTION", {Country Reference Sheet - Countries 2 Range 3},@cell>=DATE(2022,1,1),{Country Reference Sheet - Countries 2 Range 3},@cell<=DATE(2022.3.31))

just checking, but did you mean "aYes" instead of "Yes"?

Kelly

• ✭✭✭✭✭

Hi Kelly

"Ayes" was the name of the column.

Used the following formula and still comes up with Unparseable:

=COUNTIFS({Country Reference Sheet - Countries 1 Range 2}, FIND("aYES", @cell) > 0, {Country Reference Sheet - Countries 1 Range 1}, "ACTION", {Country Reference Sheet - Countries 1 Range 3}, >=DATE(2022, 1, 1), {Country Reference Sheet - Countries 1 Range 3}, <=DATE(2022, 3, 31)) + COUNTIFS({Country Reference Sheet - Countries 1 Range 2}, FIND("aYES", @cell) > 0, {Country Reference Sheet - Countries 1 Range 1}, "ACTION, {Country Reference Sheet - Countries 1 Range 3}, >=DATE(2022, 1, 1), {SSOT Reference Sheet - Countries 1 Range 3},<=DATE(2022, 3, 31))

Do I need the @cell reference you have listed above as I didn't have this in the original formula which just looked at one sheet as follows and worked fine. I have tried with and without and neither work.

=COUNTIFS({Country Reference Sheet - Countries 1 Range 2}, FIND("aYES", @cell) > 0, {Country Reference Sheet - Countries 1 Range 1}, "ACTION", {Country Reference Sheet - Countries 1 Range 3}, >=DATE(2022, 1, 1), {Country Reference Sheet - Countries 1 Range 3}, <=DATE(2022, 3, 31))

There was an error I noticed where the final date had full stops instead of comma's (2022.3.31) but changed and this still doesn't work.

Any further thoughts?

• ✭✭✭✭✭

Hi Kelly

Did you have an opportunity to review my further comments as I still cannot get the right results and the following formula still comes up with Unparseable:

=COUNTIFS({Country Reference Sheet - Countries 1 Range 2}, FIND("aYES", @cell) > 0, {Country Reference Sheet - Countries 1 Range 1}, "ACTION", {Country Reference Sheet - Countries 1 Range 3}, >=DATE(2022, 1, 1), {Country Reference Sheet - Countries 1 Range 3}, <=DATE(2022, 3, 31)) + COUNTIFS({Country Reference Sheet - Countries 1 Range 2}, FIND("aYES", @cell) > 0, {Country Reference Sheet - Countries 1 Range 1}, "ACTION, {Country Reference Sheet - Countries 1 Range 3}, >=DATE(2022, 1, 1), {SSOT Reference Sheet - Countries 1 Range 3},<=DATE(2022, 3, 31))

Do I need the @cell reference you have listed above as I didn't have this in the original formula which just looked at one sheet as follows and worked fine. I have tried with and without and neither work.

=COUNTIFS({Country Reference Sheet - Countries 1 Range 2}, FIND("aYES", @cell) > 0, {Country Reference Sheet - Countries 1 Range 1}, "ACTION", {Country Reference Sheet - Countries 1 Range 3}, >=DATE(2022, 1, 1), {Country Reference Sheet - Countries 1 Range 3}, <=DATE(2022, 3, 31))

There was an error I noticed where the final date had full stops instead of comma's (2022.3.31) but changed and this still doesn't work.

Any help much appreciated.

Kind regards

Mike

• ✭✭✭✭✭✭

I can see you're missing a closing quote after ACTION. Hopefully that is all that is needed to clear the UNPARSEABLE. No, you shouldn't need the @cell as part of the Date criteria. However, if the formula above still doesn't work after adding the quote mark, we'll try

IFERROR(@cell),0) where I have the @cell listed with all the dates. (so in front of any less than or greater than signs)

Let me know what you find

Sorry again for the delay

Kelly

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!