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

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.

-

## migrated from stackoverflow.comDec 21 '12 at 12:06

This question came from our site for professional and enthusiast programmers.

I posted an answer but note that if you are using that first formula exactly as shown then you will probably get incorrect results because of the mismatched ranges - that formula will add the value from the `next row` when the condition is satisfied, e.g. if I99 = C4 then F100 is added to the sum.....I assume you don't want it to work like that.... – barry houdini Dec 20 '12 at 12:53
@barryhoudini: correct, I was going to change my question to use `\$F:\$F` but then I saw that it confuse people reading your answer (containing `F2:F2000`). Is that where the VTC comes from? – Wikis Dec 20 '12 at 13:19
VTC? Not sure what that means.... – barry houdini Dec 20 '12 at 13:26
@barryhoudini: sorry, vote to close. – Wikis Dec 20 '12 at 13:46
OK I see - I believe that pure formula based questions should really be at SuperUser - some of these type of questions get moved there...... – barry houdini Dec 20 '12 at 14:40

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)`

-

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))}
``````
-