Count number of lines satisfied criteria

[kuldokk]

Hello everyone,

I have 3 tables from left to right (1), (2), (3), where (1) and (2) are data tables and (3) is the result I would like to have.

Code 101 and 102 in table (2) belong to product group SP. I would like to count how many lines are there where the employee A sold either Product 101 or 102 or both.

How would I do that/ What would be the formula for L3?

Thank you very much!

 

[gaz_chops]

I get a different result to you!

[TABLE="width: 460"]
<colgroup><col><col><col span="4"><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SP[/TD]
[TD]SP[/TD]
[TD]AP[/TD]
[TD]AP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SP[/TD]
[TD]AP[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S101[/TD]
[TD]S102[/TD]
[TD]S103[/TD]
[TD]S104[/TD]
[TD]S105[/TD]
[TD][/TD]
[TD]SP[/TD]
[TD="align: right"]101[/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SP[/TD]
[TD="align: right"]102[/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]AP[/TD]
[TD="align: right"]103[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AP[/TD]
[TD="align: right"]104[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[kuldokk]

I get a different result to you!

[TABLE="width: 460"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SP[/TD]
[TD]SP[/TD]
[TD]AP[/TD]
[TD]AP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SP[/TD]
[TD]AP[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S101[/TD]
[TD]S102[/TD]
[TD]S103[/TD]
[TD]S104[/TD]
[TD]S105[/TD]
[TD][/TD]
[TD]SP[/TD]
[TD="align: right"]101[/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SP[/TD]
[TD="align: right"]102[/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]AP[/TD]
[TD="align: right"]103[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AP[/TD]
[TD="align: right"]104[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you for your response, but no. That's not the result I want. The last two lines of tables (1) should not be counted because they are products with code 105, which does not fall in to the category SP or AP.
Again, table (3) is the result I would like to have. The logic here is:
For A:
look at each line, if A sold product 101 or 102 or both >> count as 1
>> so lines no 4,5,6,10 will be counted ---> 4
The same logic applies for Employee B.

 

[jamesblack90]

There's probably an easier way, but if it were me, I'd probably put in another column between S101 and S102 which could be
=IF(OR(B4="x", C4="x"), "x", "")
copy it down, do the same for S103 and S104 (you can hide those columns) and then do your COUNTIF(newcolumn,"x")

Otherwise you'd need to do it in VBA

 

[kuldokk]

There's probably an easier way, but if it were me, I'd probably put in another column between S101 and S102 which could be
=IF(OR(B4="x", C4="x"), "x", "")
copy it down, do the same for S103 and S104 (you can hide those columns) and then do your COUNTIF(newcolumn,"x")

Otherwise you'd need to do it in VBA

Thank you for the reply. I would rather not go for a VBA solution at this time. Adding a new column would be a choice but unfortunately if I need to do it that way, there will be a lot of extra columns, which is not optimal.

 

[jamesblack90]

Played around a bit... Try this:

=COUNTIF(B3:C11, "x") - SUMPRODUCT((B3:B11="x")*(C3:C11="x"))

 

[kuldokk]

Played around a bit... Try this:

=COUNTIF(B3:C11, "x") - SUMPRODUCT((B3:B11="x")*(C3:C11="x"))

Thank you, but it results in 6, not 4. This was my idea, but it is not working, the AND function does not return a list of TRUE, FALSE values.

{=countif(AND(A4:A13=$K$3;OR(NOT(ISBLANK(B4:B13));NOT(ISBLANK(C4:C13))));TRUE)}

Any idea?

 

[jamesblack90]

Strange, my formula above worked for me.

I guess you could play around with transpositions and matrix multiplication but I'm not great at that.

 

[RoryA]

Perhaps:
=SUMPRODUCT((A3:A11=$K$3)*(((B3:B11="x")+(C3:C11="x"))>0))

 

[kuldokk]

Perhaps:
=SUMPRODUCT((A3:A11=$K$3)*(((B3:B11="x")+(C3:C11="x"))>0))

Thanks RoryA, It is in a good direction, I had tried sum product with this

=SUMPRODUCT(--($A$4:$A$13=$K3);--(NOT(ISBLANK($B$4:$B$13)))+--(NOT(ISBLANK($C$4:$C$13)))-(--(NOT(ISBLANK($B$4:$B$13)))*--(NOT(ISBLANK($C$4:$C$13)))))

But the problem is: I need to manually select the criteria for the formula instead of doing it through table (2) because the "real" file of this example will have a decent amount of columns. I would need some thing like this:

Consider I need to fill L3:
0. In table (1): 1 line is 1 record that Employee sold something to a store.
1. L3 is the number of store that a particular employee A sold goods in category SP to.
2. With this SP, look up for code of the products in table (2), which are: 101 and 102
3. In table (1) see the lines where employee A sold something, because of the values {101,102}, we know that employee A sold product 101 or 102 or both to a store -> count this -> we have 4.

This logic is used in my vba solution, but I stuck with the formula.