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!

 

[GraH]

Hi, what would be the logic tot follow? In your sample data I can't detect why Power by Jones should be 2.
Fax could be internet, power or landline. How can one tell when it's power?

 

[mrblister]

Hi, what would be the logic tot follow? In your sample data I can't detect why Power by Jones should be 2.
Fax could be internet, power or landline. How can one tell when it's power?

In Table 1, Jones has 3 sales "transactions" (A sale of 20 faxes, a sale of 40 phones, and a sale of 80 PCs. So 3 separate sales transactions).
What I am seeking is a formula for the number of sales transactions, given the inputs of a Salesman and a Requirement. So if Salesman=Jones and Requirement=Power, the answer is 2, since Jones sold Faxes, Phones and PCs (3 sales transactions), but only Faxes and PCs need Power (2 sales transactions).

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

TABLE 1

TABLE 2

ANSWER MATRIX

2

Product

Salesman

Units Sold

Requirements

Fax

Phone

PC

Helper

Brown

Smith

Jones

Davis

3

Fax

Brown

1​

Internet

1​

1​

InternetFaxPC

Internet

2​

2​

2​

0​

4

Phone

Smith

10​

Monitor

1​

MonitorPC

Monitor

0​

1​

1​

0​

5

Fax

Jones

20​

Power

1​

1​

PowerFaxPC

Power

2​

2​

2​

0​

6

Fax

Smith

30​

Landline

1​

1​

LandlineFaxPhone

Landline

2​

2​

2​

1​

7

Phone

Jones

40​

8

PC

Smith

50​

9

Fax

Brown

60​

10

Phone

Davis

70​

11

PC

Jones

80​

I spent all night on this issue, and I have a working solution. With the help of helper-column "I", the formula in L3 is:
{=SUM(IF(COUNTIFS($I$3:$I$6,"*"&$K3&"*",$I$3:$I$6,"*"&$A$3:$A$11&"*")*($B$3:$B$11=L$2),1,0))}

The formula works... in that it gives me the solution, eventually. In my actual spreadsheet, copying the formula across and down takes forever to calculate with my real-life (large) tables (in reality, Table 1 has many rows, and the list of Requirements and Products in Table 2 are also quite large). It takes 10 minutes to get to 35% complete calculation (at which point I press escape... and the answers in the Answer Matrix magically appear but then it starts recalculating again).

So I'm still looking for either:

1. Any formula (different from my own) for the Answer Matrix that calculates more efficiently OR...
2. Suggestions on my formula to make it more efficient (see my note above).

Thanks in advance!

 

[mrblister]

Also, I don't know if this makes a difference or not (in terms of efficiency), but the formula in I3 (Helper column) is:
{=E3&CONCAT(IF(($F3:$H3=1),$F$2:$H$2,""))}
I used an array formula, because my real-life Table 2 is very long across, so a formula like "=E3&IF(F3=1,F$2,"")&IF(G3=1,G$2,"")&IF(H3=1,H$2,"")" would be very, very long. I tried turning these values static after calculation, but it didn't seem to affect the calculation time of Answer Matrix.

 

[GraH]

Hi again,

First of all thank you for making your query more explicit. That helped.
Secondly, it took me like 1 minute to solve this using Power Query. I'm wondering if you can use this too. If you'd update your profile account details with your Office version and OS I could tell.

Basically it's loading both tables to PQ, second table as connection only. Then merging both, expanding the merged table and then perform a pivot action. All actions are do-able with mouse clicks.

Loading the requirement table requires an unpivot in order to enable the merge between "Attribute" and "Product" later.

let
    Source = Excel.CurrentWorkbook(){[Name="tRequirments"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Requirements"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Int64.Type}})
in
    #"Changed Type"

Loading the Sales table and merging it with the requirements table looks like this:

let
    Source = Excel.CurrentWorkbook(){[Name="tSales"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Units Sold", Int64.Type}}),
    Merge_tRequirements = Table.NestedJoin(#"Changed Type", {"Product"}, tRequirments, {"Attribute"}, "tRequirments", JoinKind.LeftOuter),
    RemoveCols_Product_Units = Table.RemoveColumns(Merge_tRequirements,{"Product", "Units Sold"}),
    Expand_tRequirments = Table.ExpandTableColumn(RemoveCols_Product_Units, "tRequirments", {"Requirements", "Value"}, {"Requirements", "Value"}),
    PivotOnSalesmen_SumValues = Table.Pivot(Expand_tRequirments, List.Distinct(Expand_tRequirments[Salesman]), "Salesman", "Value", List.Sum)
in
    PivotOnSalesmen_SumValues

Actions used on the UI:

1. Unpivot and pivot are here
   
   
   
   
2. Merge queries
   
   
   
   
3. Second step of merge, simply click on the columns that match
   
   
4. Expanding
   
   
5. Removing columns (selection is done via CTRL + MOUSE Click)
   
   

The green table is the one loaded by PQ as Table in Excel.

When you have new data, you can simply click on Refresh (like for a pivot) to update the PQ result.

 

[Peter_SSs]

See if these formulas using standard worksheet functions do what you want.

20 05 16.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Goods	Salesman	Units Sold		Requirements	Fax	Phone	PC		No. Sales
2	Fax	Brown	1		Internet	1		1			Brown	Smith	Jones	Davis
3	Phone	Smith	10		Monitor			1		Internet	2	2	2	0
4	Fax	Jones	20		Power	1		1		Monitor	0	1	1	0
5	Fax	Smith	30		Landline	1	1			Power	2	2	2	0
6	Phone	Jones	40							Landline	2	2	2	1
7	PC	Smith	50
8	Fax	Brown	60							Units Sold
9	Phone	Davis	70								Brown	Smith	Jones	Davis
10	PC	Jones	80							Internet	61	80	100	0
11										Monitor	0	50	80	0
12										Power	61	80	100	0
13										Landline	61	40	60	70
Sales

Cell Formulas
Range	Formula
K3:N6	K3	=SUMPRODUCT(--($B$2:$B$10=K$2),--(INDEX($F$2:$H$5,MATCH($J3,$E$2:$E$5,0),MATCH($A$2:$A$10,$F$1:$H$1,0))>0))
K10:N13	K10	=SUMPRODUCT($C$2:$C$10,--($B$2:$B$10=K$9),--(INDEX($F$2:$H$5,MATCH($J10,$E$2:$E$5,0),MATCH($A$2:$A$10,$F$1:$H$1,0))>0))

 

[Peter_SSs]

If you'd update your profile account details with your Office version and OS ..

I agree with GraH - please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’).

If you have Excel 365 with the FILTER function, you could try these as I think they would be more efficient than SUMPRODUCT.

20 05 16.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Goods	Salesman	Units Sold		Requirements	Fax	Phone	PC		No. Sales
2	Fax	Brown	1		Internet	1		1			Brown	Smith	Jones	Davis
3	Phone	Smith	10		Monitor			1		Internet	2	2	2	0
4	Fax	Jones	20		Power	1		1		Monitor	0	1	1	0
5	Fax	Smith	30		Landline	1	1			Power	2	2	2	0
6	Phone	Jones	40							Landline	2	2	2	1
7	PC	Smith	50
8	Fax	Brown	60							Units Sold
9	Phone	Davis	70								Brown	Smith	Jones	Davis
10	PC	Jones	80							Internet	61	80	100	0
11										Monitor	0	50	80	0
12										Power	61	80	100	0
13										Landline	61	40	60	70
Sales (2)

Cell Formulas
Range	Formula
K3:N6	K3	=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:N13	K10	=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))

 

[GraH]

See if these formulas using standard worksheet functions do what you want.

Hi Peter_SSs, when I try your formula, which is clearly working as per your example, I get this: errors all around? Simply copied the XL2BB into my workbook. I'm also on Office 365 (Version 2016). Is this working because you are on insider? I even tried to use CSE and it's not working. Any clues where I need to look?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Goods	Salesman	Units Sold		Requirements	Fax	Phone	PC		No. Sales
2	Fax	Brown	1		Internet	1		1			Brown	Smith	Jones	Davis
3	Phone	Smith	10		Monitor			1		Internet	#VALUE!	#VALUE!	#VALUE!	#VALUE!
4	Fax	Jones	20		Power	1		1		Monitor	#VALUE!	#VALUE!	#VALUE!	#VALUE!
5	Fax	Smith	30		Landline	1	1			Power	#VALUE!	#VALUE!	#VALUE!	#VALUE!
6	Phone	Jones	40							Landline	#VALUE!	#VALUE!	#VALUE!	#VALUE!
7	PC	Smith	50
8	Fax	Brown	60							Units Sold
9	Phone	Davis	70								Brown	Smith	Jones	Davis
10	PC	Jones	80							Internet	#VALUE!	#VALUE!	#VALUE!	#VALUE!
11										Monitor	#VALUE!	#VALUE!	#VALUE!	#VALUE!
12										Power	#VALUE!	#VALUE!	#VALUE!	#VALUE!
13										Landline	#VALUE!	#VALUE!	#VALUE!	#VALUE!
Sheet2

Cell Formulas
Range	Formula
K3:N6	K3	=SUMPRODUCT(--($B$2:$B$10=K$2),--(INDEX($F$2:$H$5,MATCH($J3,$E$2:$E$5,0),MATCH($A$2:$A$10,$F$1:$H$1,0))>0))
K10:N13	K10	=SUMPRODUCT($C$2:$C$10,--($B$2:$B$10=K$9),--(INDEX($F$2:$H$5,MATCH($J10,$E$2:$E$5,0),MATCH($A$2:$A$10,$F$1:$H$1,0))>0))

 

[Peter_SSs]

Hi @Peter_SSs, when I try your formula, which is clearly working as per your example, I get this: errors all around?

Do you have the FILTER function?
Does the SUMPRODUCT version have the same issues?

Assuming that you have FILTER, what happens if you do this?
Select F2:H5 and Delete all cell values (including the 'blanks'), check the number formatting of that range is General (or Number) and re-enter the 1s

 

[GraH]

Hi Peter_SSs,
thanks for the follow-up, appreciate it.

I don't have filter yet in my arsenal. Soon I hope. As for the data formats, those were fine as I enforced number formats when I made the PQ sample.

So yes, it's both the SUMPRODUCTs that are not working.
When I run the formula evaluation all is fine until this step = SUMPRODUCT({1;0;0;0;0;0;1;0;0};1)
Clearly the dimension of both arguments are not alike, hence the error makes sense now. I can't figure out how to this is possible (it's working on your end!). I'm on EU-BE settings, would that play a role in how to build the formula too? SUMPRODUCT does keep some dark secrets hidden from me