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 Column A to A1600. In that column I have two project names "CP" and "GFI", in column B I have Project Phase 1-8, in column C I have the hours worked in each phase. The projects intertwine so how do I Sum the hours for each phase?

I tried using Conditional sum buts its summing all 1600 rows that have the same phase for both projects?

Any answers?

share|improve this question
    
Maybe a screenshot could better explain what you are talking about (post a link) – soandos Aug 25 '11 at 18:35

In Excel 2003 use SUMPRODUCT:

=SUMPRODUCT(C:C*(A:A="CP")*(B:B=1))

SUMPRODUCT works like an array formula without being one, iterating through the columns one cell at a time. If the conditionals are TRUE they are equivalent to 1, while if FALSE they are equivalent to 0, so it only ends up summing the values where both conditions are TRUE.

A simple way in Excel 2007 or newer:

=SUMIFS(C:C, A:A, "CP", B:B, 1)

Just create 16 of these, one for each set of conditions.

share|improve this answer
    
I only have excel 2003 – RichardC Aug 25 '11 at 18:51
    
@RichardC, that takes a little more work, but can be done, I'll whip something up. – Lance Roberts Aug 25 '11 at 18:53
    
@RichardC, ok, edited answer. – Lance Roberts Aug 25 '11 at 19:09

Someone may post a better answer using array formulas, but I prefer sumproduct:

 =SUMPRODUCT(($A$1:$A$1600="CP")*($B$1:$B$1600=1)*($C$1:$C$1600))
share|improve this answer
    
I think you might hit problems using sumproduct in excel 2003. – Col Aug 25 '11 at 18:57
    
Yep, SUMPRODUCT is the way. – Lance Roberts Aug 25 '11 at 19:11
    
@Col - what sort of problems? I use sumproduct pretty often for this purpose. – variant Aug 25 '11 at 19:23
    
@vairant I've only attempted it once so far and had to abandon it when a formula that worked perfectly in office 2010 generated #VALUE errors when opened in office 2003, maybe I was just unlucky or maybe it was a new feature that had been added in the later version. – Col Aug 25 '11 at 19:28
    
It's a bit sensitive - you need to ensure that the arrays are equally sized. It also bugs out when you have a non-numeric value in an array you're trying to sum. I've never had problems beyond those two. – variant Aug 25 '11 at 20:03

You must log in to answer this question.

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