Complex Counting - Count positive values in a table row with a 'Table Criteria'

[Foxu666]

Hi,

Would like to know if there's a single cell formula that would solve my Excel problem.

Would like to count (per row) how many values that is above 0 in table 2 using the criteria table (ex. add to count if Town and Juice = 1 in table 1 and positive value in Table 2)

The answer I wanted to generated can be seen in the Table, right of Table 2.

Here are sample tables.

Table 1 = Criteria Table

Criteria Table	Town	Slums	City
Juice	1	0	1
Milk	0	0	1
Bread	1	1	1
Chocolate	1	0	1

<tbody>
</tbody>

Table 2 = Main Table

Name	Type	Juice	Milk	Bread
New Vegas	Town	10	0	5
Diamond	City	15	3	10
Whiterun	Town	0	8	5

<tbody>
</tbody>

Name	Count
New Vegas	2
Diamond	3
Whiterun	1

<tbody>
</tbody>

Please note:
Table 1 is a static table.
Table 2 is pivot table meaning its dynamic therefore Columns and Rows of Table 2 may change when raw data is changed (columns may end up with a set of Milk, Bread, and Chocolate or all 4 present).

I've tried using Sumproduct and Countifs with Index Match but I cant seem to get the correct formula.

If there are other techniques other than a formula is ok, i haven't tried using pivot table using 2 data source yet.

Help is greatly appreciated.

 

[DRSteele]

Welcome to the forum.

I hope I comprehended your objective.

I created an algorithm that uses helper columns just so I could get a handle on the complex lookup requirements. I then created a single-cell formula to meet your needs.

Note that I had to create the entire set of possible food items in the Main Table to make this work. I don't know how your PivotTable will affect all this, but I think maybe you can tell the PT to include zeroed items. In any event let's try this for now.

Copy C14 across and down to F16. Copy G14 and H14 down to row 16.

	A	B	C	D	E	F	G	H
1	Criteria	Town	Slums	City
2	Juice	1	0	1
3	Milk	0	0	1
4	Bread	1	1	1
5	Chocolate	1	0	1
6
7	Name	Type	Juice	Milk	Bread	Chocolate
8	New Vegas	Town	10	0	5	0
9	Diamond	City	15	3	10	0
10	Whiterun	Town	0	8	5	0
11
12
13	Name		Juice	Milk	Bread	Chocolate	Count (helpers)	Count (single-cell)
14	New Vegas	Town	1	0	1	0	2	2
15	Diamond	City	1	1	1	0	3	3
16	Whiterun	Town	0	0	1	0	1	1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet45​

Worksheet Formulas Cell Formula C14 =IF(INDEX($B$2:$D$5,MATCH(C$7,$A$2:$A$5,0),MATCH(VLOOKUP($A14,$A$8:$B$10,2,0),$B$1:$D$1,0))*INDEX(C$8:C$10,MATCH($A14,$A$8:$A$10,0)),1,0) G14 =SUM(C14:E14) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula H14 {=SUMPRODUCT((IF(INDEX($C$8:$F$10,MATCH(A14,$A$8:$A$10,0),N(IF(1,COLUMN($C$7:$F$7)-COLUMN($C$7)+1)))*TRANSPOSE($B$2:$D$5),1,0))*(VLOOKUP(A14,$A$8:$B$10,2,0)=TRANSPOSE($B$1:$D$1)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself.

<tbody>
</tbody>