Countif by cross-referencing different tables

[mrblister]

Hi all! Would like help with the below please:

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:

1. Count the Total sales (ie count the # of rows; NOT Units Sold), of goods requiring Power, by Jones. (The answer should be 2).
2. Get Total Units Sold, of goods requiring Power, by Jones. (The answer should be 100).

Notes:

• 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!

 

[mrblister]

Secondly, it took me like 1 minute to solve this using Power Query. I'm wondering if you can use this too.

Cell FormulasRangeFormulaK3:N6K3=COUNT(FILTER($C$2:$C$10,($B$2:$B$10=K$2)*HLOOKUP($A$2:$A$10,$F$1:$H$5,MATCH($J3,$E$1:$E$5,0),0)))K10:N13K10=SUM(FILTER($C$2:$C$10,($B$2:$B$10=K$9)*HLOOKUP($A$2:$A$10,$F$1:$H$5,MATCH($J10,$E$1:$E$5,0),0),0))

Hello GraH and Peter_SSs, thank you for the replies. Sorry for the delay in my reply, I wasn't aware that I had received additional posts after my last post.

@GraH, thanks fo the introduction to Power Query.
@Peter_SSs, thanks for the formulas.

When I thought I didn't get any replies, I kept hammering at the problem, and came up with my own solution:

	A	B	C	D	E	F	G	H
1	TABLE 1				TABLE 2
2	Product	Salesman	Units Sold	Helper 4	Requirements	Fax	Phone	PC
3	Fax	Brown	1​	FaxInternetPowerLandline	Internet	1​		1​
4	Phone	Smith	10​	PhoneLandline	Monitor			1​
5	Fax	Jones	20​	FaxInternetPowerLandline	Power	1​		1​
6	Fax	Smith	30​	FaxInternetPowerLandline	Landline	1​	1​
7	Phone	Jones	40​	PhoneLandline
8	PC	Smith	50​	PCInternetMonitorPower	Helper 3	FaxInternetPowerLandline	PhoneLandline	PCInternetMonitorPower
9	Fax	Brown	60​	FaxInternetPowerLandline
10	Phone	Davis	70​	PhoneLandline
11	PC	Jones	80​	PCInternetMonitorPower
12
13	ANSWER MATRIX
14		Brown	Smith	Jones	Davis
15	Internet	2​	2​	2​	0​
16	Monitor	0​	1​	1​	0​
17	Power	2​	2​	2​	0​
18	Landline	2​	2​	2​	1​

With the assistance of Helper 3 Row and Helper 4 Column, formula in B15 is:
=COUNTIFS($D$3:$D$11,"*"&$A15&"*",$B$3:$B$11,B$14)

It works, and compared to my previous formula is fast because it's not an array formula.

Notes:

• Still kinda slow. Maybe because Helper 3 and Helper 4 formulas are array formulas (Table 1 and Table 2 are quite large in real life, so simple nested-ifs would be too huge).
• Wasn't crazy about Helper 4 column, because in real life that's on my source data sheet (so I was trying to keep formulas off of it) but it's not a big deal.

Again, thanks for the replies!

 

[Peter_SSs]

When I run the formula evaluation all is fine until this step = SUMPRODUCT({1;0;0;0;0;0;1;0;0};1)

That looks like a non-CSE formula evaluation. Do you get that in formula evaluation with CSE entry?
With CSE processing the penultimate step in the evaluation of cell K5 should be

Again, thanks for the replies!

You're welcome. Glad you are up-and-running.

 

[GraH]

That looks like a non-CSE formula evaluation. Do you get that in formula evaluation with CSE entry?
With CSE processing the penultimate step in the evaluation of cell K5 should be

View attachment 14122

It doesn't do that on my end. I've been Running the formula evaluation like you did. That's puzzling...
Anyways, thanks for the additional feedback.

 

[mrblister]

With CSE processing the penultimate step in the evaluation of cell K5 should be

Just FYI Peter_SSs I tried your formula, and my results are also different from your own. In my penultimate step, it reads: SUMPRODUCT({1;0;0;0;0;0;1;0;0},--TRUE).

My last steps in the calculation are:

1. SUMPRODUCT({1;0;0;0;0;0;1;0;0},(INDEX($F$3:$H$6,3,1)>0))*
2. SUMPRODUCT({1;0;0;0;0;0;1;0;0},--(1>0))
3. SUMPRODUCT({1;0;0;0;0;0;1;0;0},--(TRUE))
4. SUMPRODUCT({1;0;0;0;0;0;1;0;0},--TRUE)
5. #VALUE!

*- My table config is a little different from the example, but the rows and columns should be correct.

 

[Peter_SSs]

Just FYI Peter_SSs I tried your formula, and my results are also different from your own. In my penultimate step, it reads: SUMPRODUCT({1;0;0;0;0;0;1;0;0},--TRUE)

Did you confirm the formula with Ctrl+Shift+Enter, not just Enter? To be sure, select one of the formula cells & press F2 then Ctrl+Shift+Enter.
If that fixes the problem then copy that cell to the other formula cells.

 

[mrblister]

Did you confirm the formula with Ctrl+Shift+Enter, not just Enter? To be sure, select one of the formula cells & press F2 then Ctrl+Shift+Enter.
If that fixes the problem then copy that cell to the other formula cells.

Yes. I entered formula as array (Ctrl+Shift+Enter).