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

2 Answers

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

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
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.