Hi,
I have a worksheet that looks something like this:
| x | A | B | C | D | E | F |
| 1 | # of units | Tom | Jerry | Rachel | Sarah | Sam |
2 | 1 | Apple | Apple | Pear | Orange | |
| 3 | 3 | Banana | Pear | Orange | Grape | Apple |
| 4 | 4 | Orange | Apple | | | |
| 5 | 1 | Pear | | | | |
| 6 | | | | | | |
| 7 | Apple | | | | | |
The data is actually more complex and is simplified here, so it cannot be grouped more efficiently. I am trying to figure out a formula to determine how many apples there are in the entire array. In this example the answer is 9.
Currently, I am using this formula in cell B7: =SUM(SUMPRODUCT(--($A$7=$B$2:$B$5),$A$2:$A$5),SUMPRODUCT(--($A$7=$C$2:$C$5),$A$2:$A$5),SUMPRODUCT(--($A$7=$D$2:$D$5),$A$2:$A$5),SUMPRODUCT(--($A$7=$E$2:$E$5),$A$2:$A$5),SUMPRODUCT(--($A$7=$F$2:$F$5),$A$2:$A$5))
It works, but there are many more columns in my data set so as it gets larger, it gets more cumbersome. I was wondering if there was a more efficient formula that I should be using.
Thank you!
