Forum Discussion
Date format in a criteria (need month instead of date)
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.
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.
what I am looking for is the month (January, February, March) based on the column B values in Cell B4.
So that I can take the sumifs for the particular month.
ThanksSergeiBaklan
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.