Excel Date Range Issues

[regulus]

I'm trying to Count if my specified date ranges falls between certain dates.

So with other words, I'm trying to add "+1" every single time I enter a date in January 2015 in my specified Dates column. I use this simple formula;

=COUNTIF(Records!F3:F456, ">31/12/2014")

It works fine for my January cell but my issue comes when I move on to my February cell. My January cell keeps adding every entered date to itself. So I need something in that formula which tells my January cell only to look for entries made between 31/12/2014 and 31/01/2015. I've tried;

=COUNTIFS(Records!F3:F456, ">31/12/2014 <=31/01/2015") but it only takes the values down to zero.

I hope I explained this clearly enough.

 

[moogle]

You could just subtract all dates past 31/01/2015.

So that cell would read:

=COUNTIF(Records!F3:F456, ">31/12/2014") - COUNTIF(Records!F3:F456, ">31/01/2015")

So it effectively counts all that are beyond Dec 2014 but subtracts all dates in Feb 2015 and beyond. Giving you the dates only between those two i.e January.

 

[regulus]

Sweet, I actually managed to solve it a while ago with this formula;

=COUNTIFS(Records!F3:F456, ">31/12/2014", Records!F3:F456, "<31/01/2015") ---- which really is the same thing

but yours look more clean for some reason so I'll give it a shot

I don't suppose I could take the pee pee and ask one last question. In the same row of the 'January' date I enter, is a monetary value. So if I enter my date in Column F, the Column J on the same row has a dollar value I manually enter. How do I add those all up for the month if there's 10+ rows that's all connected to January?

 

[moogle]

I don't suppose I could take the pee pee and ask one last question. In the same row of the 'January' date I enter, is a monetary value. So if I enter my date in Column F, the Column J on the same row has a dollar value I manually enter. How do I add those all up for the month if there's 10+ rows that's all connected to January?

You could use

=SUMIF(Records!F3:F456, ">31/12/2014", Records!J3:J456) - SUMIF(Records!F3:F456, ">31/01/2015", Records!J3:J456)

Which essentially does the same as my previous example. It checks if the date entered is past Dec 2014, and SUMS up the items in the respective J column. It then does the same for anything past Jan 2015 and overall the code subtracts the two to get the SUM for only Jan 2015.

You could also use the SUMIFS like your COUNTIFS snippet and do:

=SUMIFS(Records!J3:J456, Records!F3:F456, ">31/12/2014", Records!F3:F456, "<01/02/2015")

The second date is in Feb because using the less than "<" operator doesn't count the date including 01/02/2015.

Your COUNTIFS example excludes 31/01/2015 as a date because it's only counting dates less than 31st Jan 2015.

HTH

 

[regulus]

Lovely, it works great. Thanks for the pointer mate!