Google-sheets – Google Sheets – Selective Totals

google sheets

I have a sheet where two columns are limited to 30 possible values, and within those two columns, each row will contain two different values.

Those 30 values come from two categories of 15, which are further subdivided into three categories of 5 values each. I've figured out how to total the number of times each individual value appears, but I also need to total the number of times values from each category & subcategory appear in a row.

The part I'm having trouble with is when two values from the same category are in the same row. I need the total to still only increase by 1 instead of 2 in this case, but I'm stumped on how to accomplish that. I've been trying with SUMIFS, but either I'm not getting the formula right, or that isn't the right function.

Any help would be greatly appreciated!

EDIT: Adding a screenshot and some more details from the comments. Should have done that from the start.

screenshot

Best Answer

Try

L3: 

=SUMPRODUCT((IFERROR(MATCH(B:B,K4:K8,0))+IFERROR(MATCH(C:C,K4:K8,0))>0)*IF(D:D="Complete Game",1,0.5))
  • MATCH to find matched cells
  • SUMPRODUCT to add the matched cells
Related Topic