# Countifs Results Problem

#### rex759

##### Well-known Member
Hello,
I want to show the message “>3” for all dates involved if the date inputted falls between a 30 day period.

In the example below, I want the dates 2/1/16 & 2/22/16 to also show the message “>3” because they are part of the 4 dates that is causing the error.

Is there a better formula to show all dates involved than countifs to obtain these results?
Excel Workbook
ABC
1DateModeError
22/1/161
32/10/161>3
43/10/161
54/10/161
65/10/161
76/10/161
87/10/161
91/11/161
102/9/161>3
112/22/161
Sheet1
Excel 2010
Cell Formulas
RangeFormula
C2=IF(COUNTIFS(\$A\$2:\$A\$11,">"&A2-15,\$A\$2:\$A\$11,"<"&A2+15,\$B\$2:\$B\$11,"="&B2)>3,">3","")
C3=IF(COUNTIFS(\$A\$2:\$A\$11,">"&A3-15,\$A\$2:\$A\$11,"<"&A3+15,\$B\$2:\$B\$11,"="&B3)>3,">3","")
C4=IF(COUNTIFS(\$A\$2:\$A\$11,">"&A4-15,\$A\$2:\$A\$11,"<"&A4+15,\$B\$2:\$B\$11,"="&B4)>3,">3","")
C5=IF(COUNTIFS(\$A\$2:\$A\$11,">"&A5-15,\$A\$2:\$A\$11,"<"&A5+15,\$B\$2:\$B\$11,"="&B5)>3,">3","")
C6=IF(COUNTIFS(\$A\$2:\$A\$11,">"&A6-15,\$A\$2:\$A\$11,"<"&A6+15,\$B\$2:\$B\$11,"="&B6)>3,">3","")
C7=IF(COUNTIFS(\$A\$2:\$A\$11,">"&A7-15,\$A\$2:\$A\$11,"<"&A7+15,\$B\$2:\$B\$11,"="&B7)>3,">3","")
C8=IF(COUNTIFS(\$A\$2:\$A\$11,">"&A8-15,\$A\$2:\$A\$11,"<"&A8+15,\$B\$2:\$B\$11,"="&B8)>3,">3","")
C9=IF(COUNTIFS(\$A\$2:\$A\$11,">"&A9-15,\$A\$2:\$A\$11,"<"&A9+15,\$B\$2:\$B\$11,"="&B9)>3,">3","")
C10=IF(COUNTIFS(\$A\$2:\$A\$11,">"&A10-15,\$A\$2:\$A\$11,"<"&A10+15,\$B\$2:\$B\$11,"="&B10)>3,">3","")
C11=IF(COUNTIFS(\$A\$2:\$A\$11,">"&A11-15,\$A\$2:\$A\$11,"<"&A11+15,\$B\$2:\$B\$11,"="&B11)>3,">3","")

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### admiral100

##### Well-known Member
Hi,

You can add "=" to the formula
=IF(COUNTIFS(\$A\$2:\$A\$11,">"&A2-15,\$A\$2:\$A\$11,"<"&A2+15,\$B\$2:\$B\$11,"="&B2)>=3,">3","")

#### rex759

##### Well-known Member
Hello and thank you for responding. This is much closer but if it only = 3, I don't want the message to appear. I only want to see the message if >3.

#### admiral100

##### Well-known Member
For C11 (2/22/16) there are only 3 dates that return TRUE for your formula....and that is why you don't see the “>3”

#### rex759

##### Well-known Member
Correct but I don't want to see the rest of the ">3"if it only equals 3. Does that make sense? The error should truly show when there is 4 or more.

#### rex759

##### Well-known Member
For example, if A11 = 2/22, I should see any errors. If A11= 2/15, the error should show in C2, C3, C10 & C11

#### rex759

##### Well-known Member
*correction
For example, if A11 = 2/22, I shouldn't see any errors. If A11= 2/15, the error should show in C2, C3, C10 & C11

#### admiral100

##### Well-known Member
 Date Mode Error Nmber of 01/02/16 1 3 10/02/16 1 >3 4 10/03/16 1 1 10/04/16 1 1 10/05/16 1 1 10/06/16 1 1 10/07/16 1 1 11/01/16 1 1 09/02/16 1 >3 4 22/02/16 1 3

<colgroup><col width="70" span="4" style="width:52pt"> </colgroup><tbody>
</tbody>
As you can see only if it's >3 it show the ">3"

#### rex759

##### Well-known Member
So in your example, I would want to see the 4 cells that make up that red 4. Maybe countifs isn't the way to go

#### admiral100

##### Well-known Member
 Date Mode Error Number of 01/02/16 1 >3 4 10/02/16 1 >3 4 10/03/16 1 1 10/04/16 1 1 10/05/16 1 1 10/06/16 1 1 10/07/16 1 1 11/01/16 1 1 09/02/16 1 >3 4 15/02/16 1 >3 4

<tbody>
</tbody>

If you chnage A11 to 2/15 then 4 cells are >3

Last edited:

Replies
1
Views
285
Replies
15
Views
489
Replies
2
Views
293
Replies
3
Views
170
Replies
4
Views
387

Threads
1,191,117
Messages
5,984,741
Members
439,906
Latest member
Sekiro1899

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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