return zero if same month?

[sundodger]

Hi all i currently have this formula.
=COUNTIFS('Call Data'!$B:$B,"UserName", 'Call Data'!$F:$F,"Calltype1", 'Call Data'!$Q:$Q,">"&'Current Month'!E$3, 'Call Data'!$Q:$Q,"<="&'Current Month'!F$3)
The cells E3 and F3 have weekending dates in them e.g 27/07/2018 and 03/08/2018
what i want to add/change is that if F3-6 is not in the same month as E3 then it returns a zero value.
thanks.
Mark

 

[steve the fish]

Hi. I think im right in saying thats only possible if E3 is the last day of the month so perhaps:

=IF(E3<>EOMONTH(E3,0),COUNTIFS('Call Data'!$B:$B,"UserName", 'Call Data'!$F:$F,"Calltype1", 'Call Data'!$Q:$Q,">"&'Current Month'!E$3, 'Call Data'!$Q:$Q,"<="&'Current Month'!F$3),0)

 

[sundodger]

Thanks you got me going in the right direction, i edited your suggestion to this.
=IF($F$3-6<>MONTH($E$3),COUNTIFS('Call Data'!$B:$B,"UserName", 'Call Data'!$F:$F,"Calltype1", 'Call Data'!$Q:$Q,">"&'Current Month'!E$3, 'Call Data'!$Q:$Q,"<="&'Current Month'!F$3),0)
worked for every month except January.
More context might help.
the sheet is pulling call data based on week ending on Fridays. it automatically generates first Friday and the other 4 weeks come from that.
The problem i have is because i have 5 weeks to allow for those months that have 5 fridays it messes up another calculation elsewhere. Hence my plan is to return zero if last date (F3) is not in the same month as E3.
the above is very close

 

[steve the fish]

You need to wrap F3-6 in MONTH also. MONTH(F3-6)

 

[sundodger]

Thanks Steve. So now i have
=IF(MONTH($F$3-6)<>MONTH($E$3),COUNTIFS('Call Data'!$B:$B,"UserName", 'Call Data'!$F:$F,"Calltype1", 'Call Data'!$Q:$Q,">"&'Current Month'!E$3, 'Call Data'!$Q:$Q,"<="&'Current Month'!F$3),0)
Works perfect with one odd exception which is March so F3 has 30/03/2018 and E3 has 23/03/2018 so it should Count the data.
It returns a zero figure.
any idea why? have to admit i am scratching my head on this one.

 

[steve the fish]

You have the zero and the countifs in the wrong place dont you? You said if F3 minus six days was not the same month as E3 then produce 0. Your formula is doing the opposite.

 

[sundodger]

Sorry but my lack of knowledge is now coming to the fore.
i have the actual sheet with the years info up to last week and the formula works perfectly for every month except March.
My understanding of how the above works is if the month of F3-6 (30/03/2018-6) is not equal to month of E3 (23/03/2018) then return a zero if not do the COUNTIF
if that is so why does it return zero when they are the same month?

 

[steve the fish]

As i said before you have to do MONTH(F3-6) not F3-6. It will (almost) never be true that F3-6 is equal to MONTH(E3). MONTH(E3) in your example is 3. The only way F3-6=MONTH(E3) is if F3 is the 9th January 1900. Very unlikely! So:

=IF(MONTH($F$3-6)=MONTH($E$3),COUNTIFS('Call Data'!$B:$B,"UserName", 'Call Data'!$F:$F,"Calltype1", 'Call Data'!$Q:$Q,">"&'Current Month'!E$3, 'Call Data'!$Q:$Q,"<="&'Current Month'!F$3),0)

is better. Now if you are seeing 0 then one of two things is happening. Either the months are not the same or the countifs formula is producing 0. It looks to me like the countifs formula could be incorrect. Whats in Call Data column Q?

 

[sundodger]

Column Q is dates data

 

[sundodger]

The above formula (with<> in it) works 100% for all months except for March in that case the one with"=" in it works. But the 30/03/2018 and the 23/03/2018 are in the same Month and that is what is displayed in the cells.
Have checked that the days in that time period has data to look at just to double check, and the fact that it displays stuff when i change to "=" from"<>" supports this.
Haunted spreadsheet methinks.