Forum Discussion
Date format in a criteria (need month instead of date)
Trying to get the month instead of the date as the criteria to get the sumifs in cell B4
Refer the attached file....
I have populated a database which shows the date of transaction on column B. (there is a filter given on Row: 9 (Cell : B9) shows month eventhough the date is entered.
I wish to do the same in cell: B4 (Instead of dates I want to provide the month) such that it gives the sumifs based on the complete month.
Can anyone show the path forward.
Thanks in advance...
In cell B4 you have concrete date, in particular 01 Jan 2020. What formula do is take this date and SUMIF() all data which are later than 31 Dec 2019 /EOMONTH(B4,-1)/ AND earlier or equal to 31 Jan 2020 /EOMONTH(B4,0)/.
Other words it sums all data for January 2020. If in B4 it will be 01 Feb 2020 it sums for February 2020.
If you need to sum for all Januaries, i.e. for January 2020 plus January 2021 plus etc. - that will be another formula.
8 Replies
- Hello, to extract Month from date, kindly use, for example, =Text(A1, "mmmm").
The above will extract the full month name from the Date in column A You may insert a month names data validation list in B4 and then have the following formula in F4 to get the desired output based on month selected in B4...
In F4
=SUMPRODUCT((TEXT(B10:B18,"mmm")=B4)*(C10:C18=C4)*(D10:D18=D4)*F10:F18)Can you please elaborate the formula.
I wish to get the month name in Cell B4 such that it becomes my criteria.
Regards
Another variant is
=SUMIFS( $F$10:$F$18, $B$10:$B$18,">"&EOMONTH($B$4,-1), $B$10:$B$18,"<="&EOMONTH($B$4,0), $C$10:$C$18,$C4, $D$10:$D$18,$D4 )if calculate for the month of given year.
