CODEWITHSUNDEEP
excelexcel-formula
bushytop
bushytop

Reputation: 391

Advanced Excel: Sum of relative cells of a category

I’m a designer. I have a list of tasks, with hours, and I’ve split them into milestones. I want to count the number of hours for each milestone.

Here’s a picture:

Excel of design time sheet

In other words: I want to look at the value in the M column, if it’s 1, I want to get the cell across it, then repeat down the rows, and sum the whole lot.

See if you can figure that one out with the least cell wastage! :) (This is Numbers for Mac, so no macros or vbscript or what-have-you.)

Upvotes: 0

Views: 1600

Answers (1)

Robert Mearns
Robert Mearns

Reputation: 11996

In Excel the SUMIF formula can be used.

Assumption: In picture above, the 'Hours' label is in cell A1 The formula for 'Milestone 1' in cell E2 would be:

=SUMIF($C2:$C11,RIGHT(E1,1),$A2:$A11)

The syntax of the formula is:

=SUMIF(range, criteria, sum range)

The criteria is determined by using the last character in the 'Milestone' heading.

Upvotes: 2

Related Questions