# Google Sheets - Selective Totals

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 My friend and I both have a goal of watching 200 ball games this season, and because I wanted to improve my ability with spreadsheet software, I started making a sheet for us to track the games we watched. B and C are the columns with the values I'm totaling (which teams played in a game I watched), and the section on the right is tracking how many times I watched each team play. Each league has 15 teams, divided into 3 divisions of 5 teams each. Commented Apr 6, 2018 at 16:41
• What's there now for the division and league totals is just a simple sum of the totals from each team, as a placeholder. I want to replace that how many games I watched that featured at least one team from a division. For example, if I watch the Red Sox play the Yankees, I want the AL East division counter to only increase by one. If I watch the Red Sox play the Cubs, then the AL East and NL Central counters should both increase by 1. Commented Apr 6, 2018 at 16:43
• The number in L3 should increase by 1 any time the column B and C cells of any given row contain at least one of the text values listed in K4:K8. Commented Apr 6, 2018 at 17:02
• Yes, if you clear formatting, A4 aligns to the right. Commented Apr 6, 2018 at 17:18

``````=SUMPRODUCT((IFERROR(MATCH(B:B,K4:K8,0))+IFERROR(MATCH(C:C,K4:K8,0))>0)*IF(D:D="Complete Game",1,0.5))