Big Sumifs, looking inside month range?

[RockandGrohl]

Hi hi hello hi.

Running into a problem where I'm looking for commission values on bookings made for a product in a newspaper, on a timeframe.

Let's say we've sold a product in Paper "Sheffield Times" and we placed the advert in the Sheffield Times on the 4th February (04/02/2019)

I'm then looking in a file called the Allocation Extracts for a booking made that matches the paper and the time of advertisement.

However, we are getting issues because of variances in advert placement times from one sheet to another, and we can't sort these issues as they're already "committed" so to speak.

What I have is a formula that looks like this, though I've removed large parts of it for ease:

=SUMIFS('[Allocation Extracts.xlsm]Om'!$F:$F,'[Allocation Extracts.xlsm]Om'!$G:$G,$B4,'[Allocation Extracts.xlsm]Om'!$B:$B,$D4)/COUNTIFS($B:$B,$B4,$D:$D,$D4)

We are summing column F (commission) in sheet OM, based on whether G = Paper Name and B = Date.

It's that date I want to change. Right now, it's looking at serial 43472, which is 07/01/2019, but what if I want it to look at: "XX/01/2019" or, any day in the given month?

Is there any way to do this? Thanks!

 

[Eric W]

The main idea if you want to use SUMIFS is to look for the start and end dates of the month. Use EOMONTH to find those dates based on your date. Try:

Book1
	A	B	C	D
1	7/14/2019		Date	Amt
2			7/2/2019	1
3	Sum		8/2/2019	2
4	10		6/1/2019	3
5			7/8/2019	4
6			7/31/2019	5
7			4/4/2018	6
Sheet3

Cell Formulas
Range	Formula
A4	A4	=SUMIFS(D2:D7,C2:C7,">="&EOMONTH(A1,-1)+1,C2:C7,"<="&EOMONTH(A1,0))

You can also use SUMPRODUCT with the MONTH function, but this should work for you.

 

[RockandGrohl]

Can a mod delete this please? I figured it out because I overlooked just making the criteria match value "">="&Date(2019,Month(B1252),1) etc etc

But it's not producing the result I wanted as the criteria is now too narrow! Oh well.