Sumproduct or sum or whatever else ??

[Graham C1600]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]User ID[/TD]
[TD]Login [/TD]
[TD]UTC[/TD]
[TD]Users[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]5[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I need to try and work out is as follows as a formula for Column D is a follows :-

Colums C is to ="No" Login is to Equal "No" How many unique values are left in Column A ?? So in this example the answer would be 1 and this is user ID 5. This is because there are some Login "Yes" answers for the user ID's. So if anybody has logged in under the User ID I do not want to count that User ID. Just UTC "No" and Logon "No" per User ID.

Secondly I need to count all users in Column D where at least 1 User ID has logged on. So this would be C="No" B="Yes". In this case the answer would be 5. User ID is a No, No and 2 and 3 are a UTC Yes. So if a User ID had a list of 20 underneath and at least one had logged on, I would count them all. I need to exclude any Use ID's where there have been no logon's.

Hope this makes sense.

Thanks

 

[Marcelo Branco]

First question
Array formula
=SUM(--(ISNA(MATCH(A2:A9,IF((B2:B9="Yes")+(C2:C9="Yes"),A2:A9),0))))
confirmed with Ctrl+Shift+Enter, not just Enter

I didn't understand the second question.
Why 5 is the expected result? Shouldn't be 6 (3 instances of user 1; 1 instance of user 3; 2 instances of user 4)?

M.

 

[DanteAmor]

Array formula for the first requirement.

{=SUM(IF(COUNTIF(A2:A9,IF(C2:C9="No",IF(B2:B9="No",A2:A9)))=1,1))}


Like Marcelo, I did not understand your second request.

 

[Graham C1600]

First question
Array formula
=SUM(--(ISNA(MATCH(A2:A9,IF((B2:B9="Yes")+(C2:C9="Yes"),A2:A9),0))))
confirmed with Ctrl+Shift+Enter, not just Enter

I didn't understand the second question.
Why 5 is the expected result? Shouldn't be 6 (3 instances of user 1; 1 instance of user 3; 2 instances of user 4)?

M.

Thanks for this. Will give it a try later.

With regards to the second question. I was going around in cirlcles all day with this yesterday. Basically i need to count column D where at least 1 person has logged per user ID. So for User ID 1 for instance the count would be 3 as 2 have logged in and 1 hasn't. I need to total all the users where at least 1 person has logged in per user ID. User ID would be 4 and i don't need to count User ID 5 as nobody has logged in. So the total for the sheet above would be 5. The UTC field also needs to be a "No".

Thanks

 

[Graham C1600]

Array formula for the first requirement.

{=SUM(IF(COUNTIF(A2:A9,IF(C2:C9="No",IF(B2:B9="No",A2:A9)))=1,1))}


Like Marcelo, I did not understand your second request.

With regards to the second question. I was going around in cirlcles all day with this yesterday. Basically i need to count column D where at least 1 person has logged per user ID. So for User ID 1 for instance the count would be 3 as 2 have logged in and 1 hasn't. I need to total all the users where at least 1 person has logged in per user ID. User ID would be 4 and i don't need to count User ID 5 as nobody has logged in. So the total for the sheet above would be 5. The UTC field also needs to be a "No".​

 

[Marcelo Branco]

Second question

Array formula
=SUM(--ISNUMBER(MATCH(A2:A9,IF((B2:B9="Yes")*(C2:C9="No"),A2:A9),0)))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[DanteAmor]

With regards to the second question. I was going around in cirlcles all day with this yesterday. Basically i need to count column D where at least 1 person has logged per user ID. So for User ID 1 for instance the count would be 3 as 2 have logged in and 1 hasn't. I need to total all the users where at least 1 person has logged in per user ID. User ID would be 4 and i don't need to count User ID 5 as nobody has logged in. So the total for the sheet above would be 5. The UTC field also needs to be a "No".​

Try this array formula

{=SUM(COUNTIF(A2:A9,IF(FREQUENCY(IF(B2:B9="Yes",IF(C2:C9="No",A2:A9)),A2:A9) > 0,A2:A9)))}

 

[Graham C1600]

Apologies I haven’t explained myself very clearly here. WhatI need to achieve is a total of unique values in column A based on thefollowing criteria. Column B="No" and Column C="No". Thekey part of this is as follows - Take user ID1, we cannot count this one asthere have been 2 x Yes answers. So the only user id we can count here is userid 4. As I need to count unique numbers then the answer to this would be 1.Basically i need to disregard any user ID's that do not meet the criteria 100%.

Once I get this bit sorted I will be back with a few more requests for help.

Thanks

 

[Marcelo Branco]

So the only user id we can count here is userid 4.

User ID 4 has Yes in row 7. The only user i can see without any Yes is user 5.
Could you clarify?

M.

 

[DanteAmor]

Apologies I havenÂ’t explained myself very clearly here. WhatI need to achieve is a total of unique values in column A based on thefollowing criteria. Column B="No" and Column C="No". Thekey part of this is as follows - Take user ID1, we cannot count this one asthere have been 2 x Yes answers. So the only user id we can count here is userid 4. As I need to count unique numbers then the answer to this would be 1.Basically i need to disregard any user ID's that do not meet the criteria 100%.

Once I get this bit sorted I will be back with a few more requests for help.

Thanks

​I'm confused, this is the requirement for the "First" petition.You could put examples back and explain the "First" and "Second" requests.