0

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

Improve this question
4
  • 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.
    – EleventhOcean
    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.
    – EleventhOcean
    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.
    – EleventhOcean
    Commented Apr 6, 2018 at 17:02
  • 1
    Yes, if you clear formatting, A4 aligns to the right.
    – EleventhOcean
    Commented Apr 6, 2018 at 17:18

1 Answer 1

Reset to default
0

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
Improve this answer
2
  • It looks like that's picking up the rows where both B & C match K4:K8 values, but not the ones where only B or C do. I'm trying to get L3 to increase any time B, C, or B & C in a single row contain one of the K4:K8 values.
    – EleventhOcean
    Commented Apr 6, 2018 at 17:32
  • That did it! And that helped me figure out how to improve some of the other formulas I was using too. Thanks!
    – EleventhOcean
    Commented Apr 6, 2018 at 18:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.