CODEWITHSUNDEEP
excelexcel-formula
acr
acr

Reputation: 1746

How to collect number of days from a month in excel

I have a column which will have some days from a month in dd-mm-yyyy format. What I need to get total number of days from each month mentioned in that column. The column might not contain all days from each month.

Example :

Column A :

2017-01-01
2017-01-02
2017-01-17
2017-01-27
2017-02-08

In above example, from 2017 Jan, I have 4 days and from Feb I have one day. I need this count for a IF statement to run in C column. ie, in C1 there will be an IF function which will check the month in A1 and count the total number of days for that specific month from A column. If that count is below a specific digit, I need to perform a formula otherwise a different one. This will repeat for all C cell

If this column contains all date from each month I can use Day function to get total days, but here how Can I collect it

Upvotes: 0

Views: 207

Answers (2)

xenteros
xenteros

Reputation: 15842

This will work: =SUMPRODUCT(1*(MONTH($A1:$A5)=D1))

enter image description here

And an example of manipulating basing on the result: =IF(SUMPRODUCT(1*(MONTH($A1:$A5)=D1))<3;"good";"bad")

enter image description here

Another one when row number represents a month (JAN in C1, FEB in C2...):
=IF(SUMPRODUCT(1*(MONTH($A1:$A5)=ROW()))<3;"good";"bad") enter image description here

In your case: =IF(SUMPRODUCT(1*(MONTH($A1:$A5)=ROW()))<10;function1();function2())

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166146

enter image description here

Array formula - use Ctrl+Shift+Enter:

=SUM((MONTH($A$1:$A$5)=MONTH(A1))*(YEAR($A$1:$A$5)=YEAR(A1)))

Upvotes: 0

Related Questions