Count total enums

[Zuha]

Hi

Im trying to create a simple app. I have 3 tables: Customers RAN, Customers BAM and Products

In the form of Customers RAN i can select via dropdown a product. and with a dropwdown i can select if i have given away a product or not. 

ID	Name Customer	Product	Product given away?
1	Cust 1	Product A	Yes
2	Cust 2	Product B	No
3	Cust 3	Product A	Yes
4	Cust 4	Procuct C	Yes

In the form of Customers BAM i can select via dropdown a product. and with a dropwdown i can select if i have given away a product or not. 

ID	Name Customer	Product	Product given away?
1	Cust 1	Product C	Yes
2	Cust 2	Product B	No
3	Cust 3	Product A	Yes
4	Cust 4	Procuct C	Yes

In my other table called products i want to show data based, on the table Customers RAN and Customers BAM,  how many products i have given away (so count value "Yes" of the product and  If value no is selected than it will not to anything and will be 0):

ID	Product	Total product given away
1	Product A	3
2	Product B	0
3	Product C	3

What formula can i use in appsheet to show the result in the field Total product given away based on the Product field ?

Best regards,
Zuha

[AngeloParana]

Hello, haven't test it yet. Just want help and give it a try. Hope it would work

Count(SELECT(Customer BAM[Product],
AND(
[Product] = [_THISROW].[Product],
[Product to be given away?] = "YES")
+
Count(SELECT(Customer RAN[Product],
AND(
[Product] = [_THISROW].[Product],
[Product to be given away?] = "YES")

[WillowMobileSys]

First, I recommend NOT having Customers RAN and BAM in different tables.  Place those rows in a single table (Maybe named "Give Aways", add a column to identify RAN or BAM, and then use either Security Filters OR Slices to filter the rows depending on the view security you are attempting to achieve.  

Then in your Products table you will have a very simple expression in the "Total Product Given Away" column:

COUNT(SELECT(Give Away[ID], 
         AND([Product] = [_THISROW].[Product], 
             [Product Given Away?] = TRUE)
))

NOTE:  There recently has been some question about the usage of "Yes" in a Yes/No column.  I suggest using TRUE.