SUMIFS Function - Cannot Set Criteria Correctly

[simonj64]

Hi,

I have the following formula summing up a Cost field when 2 criteria_range settings are met as follows:

SUMIFS(tProjectChanges[Cost],tProjectChanges[Budget Item Code],D33, DATE(YEAR(tProjectChanges[Date Originated]),MONTH(tProjectChanges[Date Originated]),1),E46)

The 2 criteria_range in the formulae are performing the following:
1. Budget Item Code = value in a cell
2. Date field (Date Originated) in a table (tProjectChange) = to a calendar column value e.g.01/01/14

The problem I have is the 2nd criteria_range part of the formula is not correct and I cannot seem to get it to work - tried everything. The criteria 2 value is always in the format of day1/month/year, so I need to do the checks in the criteria_range to ensure the Date Originated values are converted to the 1st day of the month.

So its this part that does not work in the SUMIFS - DATE(YEAR(tProjectChanges[Date Originated]),MONTH(tProjectChanges[Date Originated]),1). This part does work when used in a cell, how can I get the SUMIFS Criteria_range2 to work ensuring it is converting the criteria to look at the 1st day of the month.

Hope this all makes sense,

Thanks

simonj64.

 

[gsistek]

Instead of trying to convert the range of data to the proper format, you could use one more arguement to make sure the date is within a specific range:
=SUMIFS(tProjectChanges[Cost],tProjectChanges[Budget Item Code],D33,tProjectChanges[Date Originated]," > ="&E46,tProjectChanges[Date Originated]," < ="&EOMONTH(E46,0))

*Note - remove the spaces before and after the < and > signs.

 

[simonj64]

Brillian. Thanks very much for your assistance, works as expected.