Search code examples
excelexcel-formulaexcel-2010worksheet-function

SUMIFS with nested AND, OR, and inequalities

The following formula doesn't work:

=SUMIFS(JOBS_NUMBER,JOB_TYPE,$A10,MONTH_CLAIMED,(AND(OR(">"&$H$7,"="& $H$7), OR("<"&$J$7,"=" &$J$7))),SECTOR, "=Residential")

I'm trying to sum the number of jobs:

  • if the range JOB_TYPE matches,
  • if the MONTH_CLAIMED is greater than or equal to H7 (which contains the start of the fiscal year) AND less than or equal to J7 (which is the end of the fiscal year), and
  • the SECTOR must be Residential.

The embedded And(OR(inequalities are the problem, since the following test formula returns 0:

=SUMIF(Z8, AND(OR(">"&$H$7,"="&$H$7),OR("<"&$J$7,"="&$J$7)),Y8)

where Z8 is a date between H7 and J7, and Y8 is 1.

I'd appreciate help with correcting the syntax or possible alternatives to accomplish the same task.

Solution

  • Assuming,the required Job Type is in A10, and you wish to sum relevant values in ColumnY rows 7 to 12 inclusive, this may help: 

    =SUMIFS(Y7:Y12,JOB_TYPE,A10,Z7:Z12,">="&H7,Z7:Z12,"<="&J7,Sector,"Residential")

    However the above does assume compatibility between MONTH_CLAIMED and dates elsewhere.