0

I currently have a table in a layout similar to this:

Quality | Fruit1 | Fruit2 | Fruit3
----------------------------------
Sweet   | Apple  | Orange | Pear
Firm    | Pear   | Apple  | Watermelon
Sweet   | Orange | Pear   | Melon
Soft    | Banana | Orange | Strawberry
Firm    | Apple  | Melon  | Pear
Sweet   | Melon  | Pear   | Watermelon

(The actual table has many more rows, "qualities" and "fruits", though still only three "Fruit#" columns and the one "Quality" column.)

And am trying to create another table, such that it records (for the above example) the number of occurrences for each fruit per quality:

           |  Firm  |  Soft  | Sweet
--------------------------------------
Apple      | 2      | 0      | 1
Banana     | 0      | 1      | 0
Melon      | 1      | 0      | 2
Orange     | 0      | 1      | 2
Pear       | 2      | 0      | 3
Strawberry | 0      | 1      | 0
Watermelon | 1      | 0      | 1

(The qualities and fruits are deliberately sorted in this table.)

I've tried a number of combinations with VLookup, Match, Index and Countif/s, and I feel like I'm missing something blatantly obvious as nothing has worked as yet.

My thought process for the formula would be something like (in the case of counting Apple occurrences in Firm rows):

  • Check Quality for Firm
  • Check rows with Firm for Apple
  • If an Apple is found, add 1 to the count of Firm:Apple in the output table.

I would also prefer to avoid functions if possible, though I am unaware if this is even possible with the current layouts.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Auhsoj100
  • 7
  • 2
  • It looks like that example utilises functions, which I'd honestly prefer to avoid unless totally necessary. I was thinking of some sort of formula which would check for e.g. `Firm` in `Quality`, then check along the corresponding row/s for e.g. `Apple`, and add 1 to the count for `Firm:Apple` in the output table every time `Apple` is found in a `Firm` row. (I should probably note this in the question, actually.) – Auhsoj100 Apr 29 '15 at 02:50

1 Answers1

0

With row and column labels set up as required for the output and assuming Quality is in A1, and Firm for the output in G1 then (using functions!) please try: 

=countifs($B:$B,$F2,$A:$A,G$1)+countifs($C:$C,$F2,$A:$A,G$1)+countifs($D:$D,$F2,$A:$A,G$1)

in G2 copied across and down to suit.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 1
    See what I mean by 'blatantly obvious'? Thanks! I came across a solution in the form of summing three`countif(filter(...)...)`, however it's fairly clear your answer is probably a bit more suitable! – Auhsoj100 Apr 29 '15 at 04:00