# Countif issues

#### Newbienew

##### Active Member
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
ABCDEFGHIJKLMNOPQRSTUVW
4TITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLE
5NameenlYES
6
7NameaffYES
8
9Name2c/oYES
10
11Name3.YES
12
13
14
15
16
17
18
19
20
21
22
23NAME0
24NAME0
25NAME20
26NAME30
Sheet1
Cell Formulas
RangeFormula
B23:B26B23=COUNTIFS(\$A\$5:\$A\$18,A23,W5:W18,COUNTA(W5,W7,W9,W11))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A5:V18Expression=IF(OR(\$V4="c/o",\$V5="c/o"),"TRUE","FALSE")textNO
A5:V18Expression=IF(\$V5="enl","TRUE","FALSE")textNO
A5:V18Expression=IF(OR(\$V4="aff",\$V5="aff"),"TRUE","FALSE")textNO

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
ABCDEFGHIJKLMNOPQRSTUVW
4TITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLE
5NameenlYES
6
7NameaffYES
8
9Name2c/oYES
10
11Name3.YES
12
21
22
23NAME2
24NAME2
25NAME21
26NAME31
Sheet1
Cell Formulas
RangeFormula
B23:B26B23=COUNTIFS(\$A\$5:\$A\$18,A23,\$W\$5:\$W\$18,"Yes")

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.

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
ABCDEFGHIJKLMNOPQRSTUVW
1
2
3
4TITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLETITLE
5NameenlYES
6
7NameaffYES
8
9Name2c/oYES
10
11Name3.YES
12
13Name4YES
14
15Name416-Jun-21
16
17
18
19
20
21
22
23NAME2
24NAME2
25NAME21
26NAME31
27NAME42
Sheet1
Cell Formulas
RangeFormula
B23:B27B23=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
CellConditionCell FormatStop If True
A5:V18Expression=OR(\$V4="c/o",\$V5="c/o")textNO
A5:V18Expression=OR(\$V5="enl",\$V4="enl",\$V5="aff",\$V4="aff")textNO

@ 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

@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 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.

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

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.

Replies
2
Views
661
Replies
1
Views
217
Replies
6
Views
219
Replies
9
Views
354
Replies
5
Views
158

1,203,174
Messages
6,053,908
Members
444,694
Latest member
JacquiDaly

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back