Does AND work in Excel spreadsheet formulae using Ctrl-Shift-Enter?

Question

I have an Excel (2010) cell with this formula:

{=SUM(IF('Tasks'!$I:$I=$C4,'Tasks'!$F$2:$F$2000,0))}

which works (the correct total is given). But when I enter another item to search for:

{=SUM(IF(AND('Tasks'!$H:$H=B4,'Tasks'!$I:$I=$C4),'Tasks'!$F$2:$F$2000,0))}

it does not work (total is zero). Does AND not work in this case? Or have I made an error?

Explanation of code: I have two sheets, a Total sheet (where this code is placed) and a Tasks sheet. The Tasks sheet contains work priority (in H column), people (in I column) and estimated time (in F column). So I want to find out, per person and per tasks priority, how much work is estimated. The first line of code is just estimated time per person which, as I wrote, works fine. On the Total sheet, B4 contains the priority of the current item and C4 the name of the current person.

Answer 1

In short, No, AND doesn't do what you need here because it returns a single result (only TRUE if all conditions for all cells are TRUE) rather than an "array" of TRUE/FALSE values for each cell in the range.

You need to use * (to multiply and simulate AND) or use multiple IFs

Also you should keep the ranges the same size, in which case you can use this "array formula"

=SUM(IF(('Tasks'!$H$2:$H$2000=B4)*('Tasks'!$I$2:$I$2000=$C4),'Tasks'!$F$2:$F$2000))

confirmed with CTRL+SHIFT+ENTER

....but in Excel 2010 SUMIFS would be preferable (with sum range at the start), i.e.

=SUMIFS('Tasks'!F:F,'Tasks'!H:H,B4,'Tasks'!I:I,C4)

Answer 2

You can use a nested IF like so:

{=SUM(IF('Tasks'!$H:$H=B4, if('Tasks'!$I:$I=$C4, 'Tasks'!$F$2:$F$2000,0), 0))}