PDA

View Full Version : Solved: Sum if or sumproduct help?!



neditheg
07-16-2012, 06:01 AM
Hello guys!

I've attached an excel file to make this easier.
I'm using this file to centralize training hours for each employee.
There are many projects which can be chosen from a drop down list.
For example I have

Ababei Marilena who has on 1st January 5 hours training on 800casa project, 3 hours training on Ass. Voce T. project the next day, then on 3rd of January she has 3 hours of training on 800casa and Wednesday 4th January she has 2 hours on Ass. Mail T. project.

In the second sheet I want to get the total hours of training for my employees grouped by project. I need a formula to make this. I've thought about an sum(if ..statement but I didn't got the right results.


thanks!

CodeNinja
07-16-2012, 10:21 AM
neditheg,
This one was a real toughie and made me really think about array formulas, so thanks for the challenge.

For this, you need an array formula. You can see more information about array formulas here: http://www.cpearson.com/excel/ArrayFormulas.aspx

To get it to work you need to do the following:

1- Copy the following formula into cell F5:
=SUM(IF(Sheet1!$E4:$BN4=Sheet2!F$4,TRUE,FALSE)*(IF(ISNUMBER(Sheet1!$F4:$BO4 ),Sheet1!$F4:$BO4,0)))

2- type f2 to get back into the cell formula edit

3- hold ctrl, shift and press enter then let go.

4- you should see curly or french brackets {} around the entire formula and it should display 8.0

5- Once you have done all this, copy and paste cell F5 to the remainder of the range.

Bob Phillips
07-16-2012, 10:23 AM
Use

=SUMIF(Sheet1!$E4:$BM4,F$4,Sheet1!$F4:$BN4)

CodeNinja
07-16-2012, 10:25 AM
Heh, I hate it when you come up with a simpler (and better) solution xld. :bow:

neditheg
07-16-2012, 10:50 AM
thanks ! :)I'll try right now your solutions! :)