Sumifs between date / Check whether date is first half of month or second half

[Akbarov]

Hello Dear community,

I want to use SUMIFS but probably 1 formula can't handle it.
I have list of sales by dates. For example:
Purchase date is 10/21/2023
I need to sum between 10/16/2023 to End of that month
If Purchase date is 10/11/2023
Then I need to sum between 10/01/2023 to 10/15/2023

I need to get result whether date is first half of month or second and sum by that dates.

Hope someone can help me.
Thanks in advance!

 

[6StringJazzer]

When asking this type of question it helps to explain where your dates are (what column? what rows?) and where is the data you want to sum.

If dates are in column A and amounts are in column B

First half

=SUMIFS(B:B,A:A,">="&Date(2023,10,1),A:A,"<="&Date(2023,10,15))

second half

=SUMIFS(B:B,A:A,">="&Date(2023,15,1),A:A,"<="&Date(2023,10,31))

 

[Jeffrey Mahoney]

Please try this:

Book3
	G	H	I	J	K
1					Sum
2			Purchase Date:	10/16/2023	18
3	10/1/2023	1
4	10/2/2023	2
5	10/3/2023	3
6	10/11/2023	4
7	10/16/2023	5
8	10/23/2023	6
9	10/31/2023	7
Sheet1

Cell Formulas
Range	Formula
K2	K2	=SUMIFS(H3:H9,G3:G9,">="&(IF(DAY(J2)>15,DATE(YEAR(J2),MONTH(J2),15),DATE(YEAR(J2),MONTH(J2),1))),G3:G9,"<="&(IF(DAY(J2)>15,EOMONTH(J2,0),DATE(YEAR(J2),MONTH(J2),15))))

 

[Akbarov]

Thank you for the reply, I know to use SUMIFS, just my goal it to detect whether date is first or second half of the month and make calculations for that.
I figured out. But only problem date is showing 1 day before the day I use.
For example: secondDate = should be day 15 but it shows 14. I had same problem before, I was thinking CDate will solve it, but still have same problem. Would be glad if someonce have idea why it is like this.

                            firstDate = CDate(DateSerial(Year(ws.Range("H" & x)), Month(ws.Range("H" & x)), Day(1)))
                            secondDate = CDate(DateSerial(Year(ws.Range("H" & x)), Month(ws.Range("H" & x)), Day(15)))
                            
                            'Get Price-W
                            Dim PriceW As Double
                            PriceW = WorksheetFunction.SumIfs(wks.Range("H:H"), wks.Range("C:C"), ws.Cells(x, 9 + i).Value, _
                            wks.Range("F:F"), ">=" & firstDate, _
                            wks.Range("F:F"), "<=" & secondDate)

 

[6StringJazzer]

Don't use Day(1) and Day(15) just use 1 and 15

It would have been nice if your initial post had said you were using VBA.

 

[6StringJazzer]

Please try this:

This is a better general solution. It was not clear to me from the OP that you needed a general solution; I wrote one based just on what was in the post.

 

[Akbarov]

Thank you so much for both solutions. Jeffreys solutions is great to use as function!