Formula to count values with unique values and different criteria

[Graham C1600]

[TABLE="width: 500"]
<tbody>[TR]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[TD]d
[/TD]
[TD]e
[/TD]
[TD]f
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]John
[/TD]
[TD]Smith
[/TD]
[TD]01/01/2019
[/TD]
[TD]5/04/2019
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]12345
[/TD]
[TD]Simon
[/TD]
[TD]Taylor
[/TD]
[TD]01/01/019
[/TD]
[TD]03/12/2018
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]43433
[/TD]
[TD]June
[/TD]
[TD]Brown
[/TD]
[TD]01/01/2019
[/TD]
[TD]03/01/2019
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]234
[/TD]
[TD]Paul
[/TD]
[TD]Baker
[/TD]
[TD]01/01/2019
[/TD]
[TD][/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]12345
[/TD]
[TD]Rij
[/TD]
[TD]Veech
[/TD]
[TD]01/01/2019
[/TD]
[TD]03/03/019
[/TD]
[TD]No
[/TD]
[/TR]
</tbody>[/TABLE]

In Cell H1 I need a formula look down column F and ensure it looks for "No", then to look down column E3:E7 and where there is data, count column A3:A7. But I only need unique values. So in this case it would be 2.
In Cell H2 I need a formula look down column F and ensure is looks for "No", then to look down column E3:E7 and where there are blanks, count column A3:A7. But I only need unique values. So in this case it would be 1.
In Cell H3 I need to count all the cells in E3:E7 that contain data and column F also contains "No". This would be 3.
In Cell H4 I need to count all the cells in E3:E7 that are blank and column F also contains "No". This would be 1

Hope this makes sense.

Thanks in advance.

 

[cunningAce]

Hi Graham,

This should work...

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E	F	G	H
1	23	John	Smith	01/01/2019	05/04/2019	No		2
2	12345	Simon	Taylor	01/01/019	03/12/2018	No		1
3	43433	June	Brown	01/01/2019	03/01/2019	Yes		3
4	234	Paul	Baker	01/01/2019		No		1
5	12345	Rij	Veech	01/01/2019	03/03/019	No
Sheet1

Cell Formulas
Range	Formula
H1	=SUMPRODUCT((F1:F5="No")*(E1:E5<>"")*1/COUNTIF(A1:A5,A1:A5))
H2	=SUMPRODUCT((F1:F5="No")*(E1:E5="")*1/COUNTIF(A1:A5,A1:A5))
H3	=SUMPRODUCT((F1:F5="No")*(E1:E5<>""))
H4	=SUMPRODUCT((F1:F5="No")*(E1:E5=""))

[/FONT]

 

[Graham C1600]

Thanks CunningAce it works great. One final ask though if i may as i missed something off.
I also need to do the same so look down column F for a "No" but then count all the Cells down E. So this would equate to 4.

I tried the following but to no avail - =SUMPRODUCT((F1:F5="No")*(E1:E5="")*(E1:E5<>"")) suspect it has something to do with looking down the same column twice but i'm not sure.

Thanks

 

[cunningAce]

No problem, you are welcome...
I don't understand your last condition though...

the formula you are trying wont work as you are looking for blanks "" and none blanks "" in the same set of cells.

 

[Graham C1600]

No problem, you are welcome...
I don't understand your last condition though...

the formula you are trying wont work as you are looking for blanks "" and none blanks "" in the same set of cells.

Apologies, basically I need to count all cells with either data or blank.

 

[Graham C1600]

Apologies, basically I need to count all cells with either data or blank.

I'm getting some strange results back. Instead of returning 3 for some i'm getting 2.3. Any ideas ?? This is when i'm using the following but on different data -

=SUMPRODUCT(​

(F1:F5="No")*(E1:E5="")*1/COUNTIF(A1:A5,A1:A5)​

)

​

 

[cunningAce]

Apologies, basically I need to count all cells with either data or blank.

So this would be the same as just counting all "No's" in column F? =COUNTIF(F1:F5,"No"), Whether E has a value or not doesn't matter. The answer would be the same

 

[cunningAce]

I'm getting some strange results back. Instead of returning 3 for some i'm getting 2.3. Any ideas ?? This is when i'm using the following but on different data -

=SUMPRODUCT(​

(F1:F5="No")*(E1:E5="")*1/COUNTIF(A1:A5,A1:A5)​

)

​

I would need to see the data to see the issue here

 

[Graham C1600]

Of course it would, simple really. Many thanks.

 

[Graham C1600]

Here you go.
A B C
ID LAST_LOGIN_TIME UTC Journey
11033158 16-MAY-19 10.15.21 No 2.333333333 *=SUMPRODUCT((C2:C20="No")*(B2:B20<>"")*1/COUNTIF(A2:A20,A2:A20))
11033158 24-MAY-19 14.47.57 No
11033158 20-MAY-19 09.57.22 No
11033158 No
11033158 22-MAY-19 14.37.38 No
11033158 17-MAY-19 09.33.06 No
11033158 No
11033158 No
11033158 03-JUN-19 12.44.59 No
11033158 No
11033158 20-MAY-19 10.27.35 No
11033158 No
11127219 09-MAY-19 16.38.23 No
11127219 23-MAY-19 09.15.38 No
11127219 No
11127219 22-MAY-19 12.11.29 No
12400549 02-MAY-19 12.28.43 No
12400549 17-MAY-19 13.05.57 No
12400549 17-MAY-19 12.29.53 No

All cells are formatted as General. The 2.3333333 is the answer from the function on the right (minus the *).

Any ideas ?

Thanks