Hi all! Would like help with the below please:
TABLE 1
TABLE 2
So I'm looking for formulas to:
Thanks in advance!
TABLE 1
A | B | C | |
2 | Goods | Salesman | Units Sold |
3 | Fax | Brown | 1 |
4 | Phone | Smith | 10 |
5 | Fax | Jones | 20 |
6 | Fax | Smith | 30 |
7 | Phone | Jones | 40 |
8 | PC | Smith | 50 |
9 | Fax | Brown | 60 |
10 | Phone | Davis | 70 |
11 | PC | Jones | 80 |
TABLE 2
G | H | I | J | |
2 | Requirements | Fax | Phone | PC |
3 | Internet | 1 | 1 | |
4 | Monitor | 1 | ||
5 | Power | 1 | 1 | |
6 | Landline | 1 | 1 |
So I'm looking for formulas to:
- Count the Total sales (ie count the # of rows; NOT Units Sold), of goods requiring Power, by Jones. (The answer should be 2).
- Get Total Units Sold, of goods requiring Power, by Jones. (The answer should be 100).
- Formula 1 is more important of the two.
- The above tables are an example; my real spreadsheet has A LOT of different types of "Product" and the "Requirements" list is even larger, so I can't use a simple formula (ie have a bunch of IF statements for each good type and Requirement). Because the real tables are so large, formula efficiency may or may not be a consideration, but I'll gladly take any answer!
Thanks in advance!