Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

migrated from stackoverflow.com Dec 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
up vote 4 down vote accepted

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)

share|improve this answer

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))}
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .