CODEWITHSUNDEEP
exceldaterangecriteriasumifs
p0815
p0815

Reputation: 13

excel sumifs how to convert a range value from a day into a date so that the criteria can test if the date falls between two dates

I have a day field denoting which day of a month a debit comes out of an account. These are listed by start date end date and day number of withdrawal and value.

On a separate sheet I have a running fortnightly income and expenditure sheet and I want to include those fixed regular costs on the right fortnightly value. I currently use the following sumifs statement:

=(SUMIFS(Fixed!$F:$F,Fixed!$C:$C,"Monthly",Fixed!$D:$D,">"&DAY('monthly forecast 2017'!H1),Fixed!$D:$D,"<"&DAY('monthly forecast 2017'!I1),Fixed!$B:$B,">"&H1,Fixed!$A:$A,"<"&'monthly forecast 2017'!I1))

The worksheet Fixed! is the sheet containing the fixed regular costs.

Fixed!$F:$F - the data being calculated in the sumifs criteria range 1: Fixed!$C:$C - include if row value is "monthly"

criteria range 2: Fixed!$D:$D,">"&DAY('monthly forecast 2017'!H1) - include if day number $D which is greater than the day number of the 'from' date of the fortnight column e.g. fortnight from H1 = 01-01-17 to H2 = 14-01-17

criteria range 3: Fixed!$D:$D,"<"&DAY('monthly forecast 2017'!I1) as above but if day number $D is less than the start of the next fortnight e.g. fortnight from I1 = 01-01-17 to I2 = 14-01-17

criteria range 4 & 5: just ensures the start and end date of this repeating bill does not after or before the fortnight respectively.

The problem is that where a fortnight is in 1 month and ends in another e.g. from column F the dates from 26-02-17 to 11-03-17 will fail to work as intended when a bill falls on the 27th. As the criteria of range 3 fails to capture 2 different months. Therefore I tried to convert the day value to a date value based on the month in question e.g.

Criteria 2 becomes: DATE(year(F1),month(F1),day(Fixed!$D:$D),">"&'monthly forecast 2017'!F1 where F1 = start date of the forthnight. However this will not work due to improper use of the DATE() function on a range.

I need to either be able to convert a day to a specific date in order to check if the fixed bills falls between the two dates or I need to change the formula. I feel that latter but at the moment I cannot think of another method other than creating a VB script.

Example of fixed costs sheet

Example of calculated fortnightly costs (income/expenditure)

As you can see the first value should be in a column where the 27th day is in that fortnight. But it is not included when the fortnight ends in the next month.

Upvotes: 0

Views: 723

Answers (1)

PEagle
PEagle

Reputation: 35

I think you are overthinking your problem.

If the data in fixed is in date format (i.e. 01/02/2017 for example), excel sees it as a number. which means you can compare dates without any other calculation : this will then be your formula (for one criteria, I'll let you put in the others):

sumif(fixed!$D1:$D10,"<"&'monthly forecast 2017'!I1)

For the fornight part, you then add the number of days to your criteria.

I may not have fully understood your problem, feel free to comment to help me get it.

Upvotes: 0

Related Questions