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 sheet that keeps track of bonus for our paralegals. Because we need the clients to be listed in one sheet all the paralegals are also in one sheet instead of each having their own. I need the Excel sheet to add the amount of the bonus for each paralegal individually for which I have the formula:

=SUM(IF(A2:A35="paralegal1",F2:F35)).

Now I need to edit this formula so that it takes into account another cell but only when that cell contains data. If that cell does not contain data then I don't want it to add the bonus.

So for example this formula adds the bonus on cells F2 to F35 only when A2 to A35 show paralegal1. What I need this formula to do is add bonus from cells F2-F35 only when cells A2-A35 show paralegal1 and also cells E2-E35 show data.

So how can I modify the formula to do what I want?

share|improve this question

1 Answer 1

if you're using excel 2007 and up, use:

=sumifs($F$2:$F$35,$A$2:$A$35,"paralegal1",$e$2:$e$25,"<>")

if you're using 2003 and below, use:

=sumproduct(($A$2:$A$35="paralegal1")*($e$2:$e$25<>"")*$F$2:$F$35)

share|improve this answer

Your Answer

 
discard

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