Countif issues

[Newbienew]

Good Day,

I am looking to count the various names plus the yes entries in column "W". But I also wanted to account for if a date was enter instead of yes. I thought using the Counta would work out with the Countifs

Condition Format Issue.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
4	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE
5	Name																					enl	YES
6
7	Name																					aff	YES
8
9	Name2																					c/o	YES
10
11	Name3		.																				YES
12
13
14
15
16
17
18
19
20
21
22
23	NAME	0
24	NAME	0
25	NAME2	0
26	NAME3	0
Sheet1

Cell Formulas
Range	Formula
B23:B26	B23	=COUNTIFS($A$5:$A$18,A23,W5:W18,COUNTA(W5,W7,W9,W11))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:V18	Expression	=IF(OR($V4="c/o",$V5="c/o"),"TRUE","FALSE")	text	NO
A5:V18	Expression	=IF($V5="enl","TRUE","FALSE")	text	NO
A5:V18	Expression	=IF(OR($V4="aff",$V5="aff"),"TRUE","FALSE")	text	NO

 

[Toadstool]

Hi Newbienew,

Cells W6, W8, W10, etc. will always be null as they're merged cells so your formula for COUNTA could be
=COUNTA($W$5:$W$18)
which would return 4 because 4 cells are not empty.

Your COUNTIFS part W5:W18,COUNTA(W5,W7,W9,W11) is therefore checking how many cells in W5 to W18 contain a 4.
The COUNTIFS($A$5:$A$18,A23 additionally checks that A5 to A18 match "NAME" in cell A23.

I think you're trying to count, for each name in A23 to A26, how many times "YES" appears in W5 to W18 so try this:

Newbienew3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
4	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE
5	Name																					enl	YES
6
7	Name																					aff	YES
8
9	Name2																					c/o	YES
10
11	Name3		.																				YES
12
21
22
23	NAME	2
24	NAME	2
25	NAME2	1
26	NAME3	1
Sheet1

Cell Formulas
Range	Formula
B23:B26	B23	=COUNTIFS($A$5:$A$18,A23,$W$5:$W$18,"Yes")

 

[Newbienew]

Yes you are correct. I was also trying to account for the possiblitiy of a date being entered in using the Counta. So if a yes is entered it would count and if a date is entered would also count.
However if they cannot be combined, I may be looking at a countifs with a greater than criteria.

 

[Toadstool]

You don't say what the criteria should be for any date entered so here it checks if it "Yes" or a date was entered which is in the last 6 months.

Newbienew3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2
3
4	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE	TITLE
5	Name																					enl	YES
6
7	Name																					aff	YES
8
9	Name2																					c/o	YES
10
11	Name3		.																				YES
12
13	Name4																						YES
14
15	Name4																						16-Jun-21
16
17
18
19
20
21
22
23	NAME	2
24	NAME	2
25	NAME2	1
26	NAME3	1
27	NAME4	2
Sheet1

Cell Formulas
Range	Formula
B23:B27	B23	=COUNTIFS($A$5:$A$18,A23,$W$5:$W$18,"Yes")+COUNTIFS($A$5:$A$18,A23,$W$5:$W$18,">"&EDATE(TODAY(),-6))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:V18	Expression	=OR($V4="c/o",$V5="c/o")	text	NO
A5:V18	Expression	=OR($V5="enl",$V4="enl",$V5="aff",$V4="aff")	text	NO

 

[arthurbr]

How to use merged cells effectively

 

[Newbienew]

@ Toadstool thank you so much for your help. There are no criteria for the date to be entered. I don't know why I didnt consider the +. Thank you sooo much

 

[Newbienew]

@Toadstool Not sure if I can ask this here or If i need to make a new posting. Is it possible to do this in VBA. I am currently having a pasting issue with the CF being duplicated?

 

[Toadstool]

@Toadstool Not sure if I can ask this here or If i need to make a new posting. Is it possible to do this in VBA. I am currently having a pasting issue with the CF being duplicated?

I would post the question again tagging it as VBA and with the title saying VBA solution for colour fill needed.

 

[Newbienew]

Just realize the issue that I am having is a Condition Formatting not a countif. Please disregard. Thank you.

 

[Newbienew]

I would post the question again tagging it as VBA and with the title saying VBA solution for colour fill needed.

Will do. Thank you.