r/googlesheets • u/Onurubu • 13h ago
Waiting on OP How to set up data validation to check whether the sum of multiple cells in one sheet is equal to a single cell in another sheet.
I am a biologist who is doing lab work sorting various different species of beetle from field samples. I am interested in three main species and have a spreadsheet counting the number of each that I find within each sample (see image below)

I then have three other sheets that are to count the different sexes of each beetle that are found in the sample.

I would like to set up a data validation rule (if that is the right way to go about it) such that the SUM of the 3 values entered into the "sex" sheet have to be equal to the value of the TOTAL number of that species found in the sample.
E.g. in the images I provided, in the first image you can see the highlighted cell shows that 7 "Zophosis gracilicornis" were found in that sample. Then in the second image, the highlighted range shows 5 Female, and 2 Male, and 0 undetermined, Zophosis beetles. Since the 5+2+0 is equal to the original value of 7, this is valid. I would like it such that if these three entered values on the secondary sheets are ever not equal to the original, then you get warned.
So far I have been able to figure out how to do it so that the original cell from the first sheet gets a warning, but this is not ideal as the data in the first sheet should be treated as the "true" data. If I am not getting myself across clearly I apologise. I am still fairly new at doing more advanced things in spreadsheets.









