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

mike.thorpe17421

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.

Kelly Moore

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

mike.thorpe17421

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

Kind regards

Mike

mike.thorpe17421

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?

Thanks for your help

Regards

Mike

Kelly Moore

Hey @mike.thorpe17421

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

mike.thorpe17421

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?

mike.thorpe17421

@Kelly Moore

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

Kelly Moore

Hey @mike.thorpe17421

So sorry - I must have overlooked your reply.

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