Simplifying Excel Formula with Multiple SUMIFs

I have a workbook with three worksheets titled FY09, FY10, and FY11. I need a count of entries that have one of the following开发者_开发技巧 statuses (Column D): Complete, Cancelled. Here is what the function currently looks like:

=COUNTIFS('FY11'!D:D,"Complete",'FY11'!F:F,">="&D3,'FY11'!F:F,"<="&F3)+COUNTIFS('FY11'!D:D,"Cancelled",'FY11'!F:F,">="&D3,'FY11'!F:F,"<="&F3) + COUNTIFS('FY10'!D:D,"Complete",'FY10'!F:F,">="&D3,'FY10'!F:F,"<="&F3)+COUNTIFS('FY10'!D:D,"Cancelled",'FY10'!F:F,">="&D3,'FY10'!F:F,"<="&F3) + =COUNTIFS('FY09'!D:D,"Complete",'FY09'!F:F,">="&D3,'FY09'!F:F,"<="&F3)+COUNTIFS('FY09'!D:D,"Cancelled",'FY09'!F:F,">="&D3,'FY09'!F:F,"<="&F3)

Why can't I use something like this:

=COUNTIF('FY10'!D:D,{"Complete","Cancelled"})

---

Just a little more complex than your pseudo example, but this is about as simple as Excel will allow it:

=SUM(COUNTIFS('FY10'!D:D,{"Complete","Cancelled"}))

---

You could use an array formula to simplify it, regrettably I don't think it is as simple as the one you wanted...

This is what occurs to me you could do...

=SUM(IF(D:D="Cancelled",1,IF(D:D="Complete",1)))

remmeber to enter the formula with ctrl+shift+enter