Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
    
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
add comment

migrated from stackoverflow.com Dec 21 '12 at 12:06

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

2 Answers

up vote 3 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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.