You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
Hello. I've just transitioned to using Numbers recently and I'm having a lot of difficulty implementing equations. One such trouble that's been consuming a lot of my time is the following, something I didn't think would be so difficult to implement. I have numerous dates in one column and in another I just want to count how many dates include a specific year. The following picture is what I'm aiming for, but can't seem to do. Any help would be appreciated, thank you.
Hi NB,
COUNTIFS is your friend here.
COUNTIF can handle only one comparison. In this case you want to count occurrences that fall within a one calendar year period.
Put into words, you want the occurrences on or after the first day of the year AND before the first day of the next year.
Here's an example, using your sample data:
Column B contains the Date & Time values that are to be counted.
Cell D2 contains the number 2019, which the first DATE will use to construct the 'smallest' date for which the dates will be counted, and the second DATE will calculate the date 12 months later.
D3 contains the number of the following year, used by the copy of the formula in row 3.
Translated to English, the formula in E2 says:
Count the rows in which the date is on or after the first day of the year in D2 AND is before the first day of the month 12 months later. As the formula is filled down,the row of the D2 reference will increment by one for each row.
Copy/paste version of the formula:
COUNTIFS(B,">="&DATE(D2,1,1),B,"<"&EDATE(DATE(D2,1,1),12))
Regards,
Barry
Hi NB,
COUNTIFS is your friend here.
COUNTIF can handle only one comparison. In this case you want to count occurrences that fall within a one calendar year period.
Put into words, you want the occurrences on or after the first day of the year AND before the first day of the next year.
Here's an example, using your sample data:
Column B contains the Date & Time values that are to be counted.
Cell D2 contains the number 2019, which the first DATE will use to construct the 'smallest' date for which the dates will be counted, and the second DATE will calculate the date 12 months later.
D3 contains the number of the following year, used by the copy of the formula in row 3.
Translated to English, the formula in E2 says:
Count the rows in which the date is on or after the first day of the year in D2 AND is before the first day of the month 12 months later. As the formula is filled down,the row of the D2 reference will increment by one for each row.
Copy/paste version of the formula:
COUNTIFS(B,">="&DATE(D2,1,1),B,"<"&EDATE(DATE(D2,1,1),12))
Regards,
Barry
NotchBook wrote:
A bit more complex than I was originally thinking
If you don't like the complexity of the formulas be sure to look into Pivot Tables (Organize > Create Pivot Table). They will give you the count by year with just a few clicks. No formulas. Done in a few seconds.
SG
A slightly less complex formula is
=COUNTIFS(A,">="&DATE(C2,1,1),A,"<"&DATE(C2+1,1,1))
In English it reads "count if the date is greater than or equal to Jan 1 of the specified year and the date is less than Jan 1 of the next year"
Pivot table are for sure very handy and save a lot of formulas (often very complicated formulas using a few extra columns). One disadvantage is they do not update automatically if/when the data changes; you have to manually click to refresh the data.
Yeah, that quick click on the 'refresh' icon is really tough! 😀
Seriously, Pivot Tables save a LOT of work in many situations. Both in Numbers and in Excel. Certainly in this particular situation they are far superior.
SG
This took about 30 seconds of clicks and drags, less than the time to just type in a formula, let alone figure out what the formula should be and then debug it to get it working:
SG
Thanks Berry! A bit more complex than I was originally thinking, but thanks again.
Using COUNTIF with Dates
