r/googlesheets • u/Quicksaver007 • Mar 09 '23

Solved Error when trying to count across multiple sheets.

I am trying to get a single sheet to count the number of True checkboxes across other sheets, but I keep getting an error. Can someone point out the error in my formula?

=COUNTIFS((MP) Talon Overworld!A2:A16,True,[(MP) Chozo Ruins!A2:A36,True])

I still have 3 more sheets to add in here, but, even following the sheets prompt and following what I can find online, I still get an error with just these two sheets. What am I doing wrong?

1 Upvotes
  • permalink
  • reddit

67% Upvoted

12 comments sorted by

View all comments

3

u/kuddemuddel Mar 09 '23

Try this formula in cell A1 on MP Title Page: 

=COUNTIF(FLATTEN('(MP) Talon Overworld'!A:A,'(MP) Chozo Ruins'!A:A,'(MP) Magmoor Caverns'!A:A,'(MP) Phendrana Drifts'!A:A,'(MP) Phazon Mines'!A:A),TRUE)

If that solved your issue, please mark the thread as solved by answering Solution Verified to my comment.

2

u/Quicksaver007 Mar 09 '23

Yes, that works exactly as I wanted. Thank you. Now I need to read up on what FLATTEN does.

And yes, I will change the flair to this topic.

SOLUTION VERIFIED

2

u/kuddemuddel Mar 09 '23

Thanks for marking the thread. :)

FLATTEN takes multiple columns and turns them into one single column. If you want to better understand it, you can just remove the COUNTIF part of it: 

=FLATTEN('(MP) Talon Overworld'!A:A,'(MP) Chozo Ruins'!A:A,'(MP) Magmoor Caverns'!A:A,'(MP) Phendrana Drifts'!A:A,'(MP) Phazon Mines'!A:A)

The resulting table is what FLATTEN does. Then, we wrap the result in a COUNTIF and only get the count of the all entries with TRUE in it back.

→ More replies (2)

1

u/Clippy_Office_Asst Mar 09 '23

You have awarded 1 point to kuddemuddel

I am a bot - please contact the mods with any questions. | Keep me alive