CODEWITHSUNDEEP
excelexcel-formula
hblrvce
hblrvce

Reputation: 13

In Excel how to count between date1 and date2 that have cells in row that contain text?

I need to choose cells in one column that are between two dates, and then based on the rows that contain those dates, choose cells in another row that also contains content.

I didn't use ISBLANK because it counts a formula yet an empty cell as a not-blank. Instead check if there is content by "*".

Here is what I came up with, but instead of returning the number of cells, instead this returns TRUE (which obviously isn't what I want).

In the formula below I am assuming:

My goal is to:

See this picture of a sample excel sheet to make my intent clear:

excel sheet

How can I get my formula working so it does as needed?

SOLUTION

Hi all, so thanks to @girlvsdata, we have a working solution. I had to do a couple edits to her code to work for my uses, but her formula overall works perfect. Here is the solution:

To choose all cells in column E that are not blank, in between the date range of all of January (unknown end date) based on the adjacent C column if that is your date column, then the solution is:

=IF(COUNTIFS(C:C,">="&"2018-1-1",C:C,"<="&EOMONTH("2018-1-1",0),E:E,"*")=0,"",COUNTIFS(C:C,">="&"2018-1-1",C:C,"<="&EOMONTH("2018-1-1",0),E:E,"*"))

Note that "2018-1-1" is January 1 2018, and EOMONTH("2018-1-1",0) is the last valid day of January in the year 2018 (in this case, 31, but if it is different another year (e.g. for February this works for leap years too) then it will be that last day). Also it eliminates the need to calculate which is the last day or every month, as well as months that have changing end dates dependent on the year (e.g. Feb). This is important to eliminate a margin of error.

The only thing you have to do to change the month is only change e.g. -1- (Jan) to -2- for Feb, or change the year for other years. With this formula you can ignore the day part.

If the answer is 0 (no cells have any content in between the range), then the cell is blank instead of 0. (GOod for when you want to create a sheet checking future dates for future reference when more rows are added to the sheet.

It also works across different sheets, just use, say your other sheet is called "Tracker" then use Tracker!C:C and Tracker!E:E. Hope it helps!

Thank you all! :D

Upvotes: 1

Views: 1017

Answers (1)

girlvsdata
girlvsdata

Reputation: 1644

(Please note: My local date format is day, then month)

With the data laid out as in your example above:

       A          B
1     Dates     |Content
    ------------+-------
2   1/01/2018   |
3   2/01/2018   |123456
4   3/01/2018   |
5   4/01/2018   |12398
6   5/01/2018   |484
7   6/01/2018   |1538
8   7/01/2018   |
9   8/01/2018   |   
10  9/01/2018   |
11  10/01/2018  |14648
12  11/01/2018  |
13  12/01/2018  |145615
14  13/01/2018  |

And with the date range in cells D2 and E2:

Date Start  Date End
2/01/2018   7/01/2018

This formula returns the count:

=COUNTIFS(A:A,">="&D2,A:A,"<="&E2,B:B,">0")

This will depend on whether your numbers in Column B are formatted as text or number. If they are formatted as numbers, the above formula will work. If they are formatted as text, replace the last section ">0" with "*".

This formula adds the conditional part of your question:

=IF(COUNTIFS(A:A,">="&D2,A:A,"<="&E2,B:B,">0")=0,"",COUNTIFS(A:A,">="&D2,A:A,"<="&E2,B:B,">0"))

(If the formula returns 0, show blank)

Upvotes: 1

Related Questions