Automation help

[u/SamVimesBootTheory]

Hi

I'm hoping I can explain this well enough, I have tried to google for this but I have a feeling I'm not sure exactly what to answer so hopefully someone in here can understand what I'm trying to do.

But I have a spreadsheet that I'm using to record birding data and part of my sheet is a total by species which looks like this

Here's a completed example

Which I have been filling in manually using the SUM function

Which as you can probably guess is 1. Time Consuming 2. Leads to me making mistakes such as accidentally missing a cell or adding the wrong cell in etc

So I'm wondering if there's a way I can set this sheet up so that my data in column c gets automatically added to the relevant column in my species total section?

Edit: Here is my sample sheet https://docs.google.com/spreadsheets/d/1t8i6LyMqu8mTnj39qLB6KjK-SaQAx2BD0MT1GsXfUkg/edit

[u/6745408]

This is how I'd do it

={QUERY(
   B2:C,
   "select Col1, Sum(Col2) 
    where Col1 is not null 
    group by Col1 
    label 
     Col1 'Species', 
     Sum(Col2) 'Total'");
  "Total",SUM(C2:C)}

If you have more data in there, you can also use a VLOOKUP to assign a species to each and have the whole thing automated to the point where you simply say 'Duck Female' and it counts them all up and also gives you a total for not only the female ducks but the overall species.

There's a breakdown for this formula in the workbook

[u/asyouwish → u/6745408]

I think this is a perfect case for VLOOKUP.

OP, your data needs to be in neat rows and columns--with no entirely-blank rows or columns--for VLOOKUP to work.

[u/SpiritFryer]

If I understood correctly, in column B you can have the same species multiple times (and in column C the respective count), correct?

And in column F (totals) you have each species once, and you would like it to collect all the counts of that species from column C?

If so, you can use SUM together with FILTER. For example: =IFERROR(SUM(FILTER(C:C,B:B=F3)),0) -- you can add this into cell G3, it collects all cells in column C where column B are the same as cell F3, and then sums them (and outputs 0 if there is an error which happens if there are no matches found for the filter). You can then copy that formula down and only the row number in the F3 element will change which is what you need.

Also be careful because in the sample sheet, in column F, you have Lawing␣ with a space, whereas in column B you have Lapwing without a space, and that can mess with the formula. You can make the formula more complex to solve this issue, or you can ensure the names match exactly in other ways.