Count contents of one column if an adjacent cell matches a query in Numbers

Question

I have a spreadsheet maintained in Numbers where I record my practices at the range, which lists type of weapon (pistol, rifle, shotgun), as well as the numbers of shots fired.

I am readily able to calculate the total, but want a more granular overview.

The column recording numbers of shots is called "skudd", while the one recording type of weapon is called type. The formula I've got right now is:

COUNT(Skudd;COUNTIF(Type;"Pistol"))

This should return something over 1500 shots, but currently returns the result 28. The number of times at the range shooting pistol is 18.

Answer 1

Use SUMIF instead of COUNTIF. SUMIF let's you search for a value in one range and accumulate values in another range if a match is found. COUNTIF counts the occurrences of a match, which is why you're getting lower than expected results.

Let's say I have two columns: Type and Count. Here's our sample data:

If I want to SUMIF all the Count values when Type is value A I'd use:

=SUMIF(A2:A7, "A", B2:B7)

Doing this for values A, B and C yields the correct results on our simple data set so we can convince ourselves this is what we actually want:

So, in your case, you'd want to do something like:

=SUMIF(<range where you keep gun type>, "Pistol", <range where you keep shots fired>)

And that would give you the total shots fired any time you recorded the gun type as Pistol.

Note, it's not necessary for the columns to be adjacent, which is nice. But they should have a similar range or odd things can happen.