# COUNTIF based on 2 criteria (1 being dates)

#### gjs1985

##### New Member
Hi,

So here is an example spreadsheet similar to one that I'm working on:

My issue is regarding the output in rows 13-18 and what I'm struggling to do in particular is figure out what formula I need to put into the highlighted cell B14 to produce the expected result shown (once I know this I should easily be able to manipulate it for the rest).

From my research so far, I'm wondering if it may be a combination of the COUNTIF and SUMPRODUCT formulas but I'm fresh out of ideas and now starting to doubt if it's even possible.

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
+Fluff 1.xlsm
ABC
1
212/01/2022John Smith
322/06/2021John Smith
421/01/2022Anna Thompson
507/09/2021John Smith
602/11/2021Anna Thompson
712/05/2021John Smith
822/08/2021Anna Thompson
908/06/2020John Smith
1017/11/2021Anna Thompson
1121/02/2021Anna Thompson
12
13John SmithAnna Thompson
1413
1511
1620
1701
1810
Main
Cell Formulas
RangeFormula
B14:C14B14=COUNTIFS(\$C\$2:\$C\$11,B\$13,\$B\$2:\$B\$11,"<="&TODAY(),\$B\$2:\$B\$11,">="&EDATE(TODAY(),-3))
B15:C15B15=COUNTIFS(\$C\$2:\$C\$11,B\$13,\$B\$2:\$B\$11,"<"&EDATE(TODAY(),-3),\$B\$2:\$B\$11,">="&EDATE(TODAY(),-6))
B16:C16B16=COUNTIFS(\$C\$2:\$C\$11,B\$13,\$B\$2:\$B\$11,"<"&EDATE(TODAY(),-6),\$B\$2:\$B\$11,">="&EDATE(TODAY(),-9))
B17:C17B17=COUNTIFS(\$C\$2:\$C\$11,B\$13,\$B\$2:\$B\$11,"<"&EDATE(TODAY(),-9),\$B\$2:\$B\$11,">="&EDATE(TODAY(),-12))
B18:C18B18=COUNTIFS(\$C\$2:\$C\$11,B\$13,\$B\$2:\$B\$11,"<"&EDATE(TODAY(),-12))

Replies
3
Views
302
Replies
5
Views
190
Replies
1
Views
294
Replies
2
Views
388
Replies
6
Views
358

1,203,109
Messages
6,053,566
Members
444,673
Latest member

### 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