Sumproduct with changing criteria

[Phil]

Hi


I wanted to use the sumproduct formula but with the ability to recognise when the criteria changed.


See Example below.


I'm using | as column separators.


Cost Centre | Employee | Employee% | Employee Effort

100 | Bob | 50% | 30%

100 | Jim | 50% | 20%

200 | Peter | 33% | 10%

200 | Bill | 33% | 25%

200 | Jack | 33% | 60%


So I wanted the sumproduct to be on Employee% * Employee Effort but change and pick up employees by cost centre. So a sumproduct on cost centre 100 employees, then a sumproduct on cost centre 200 employees.


So I can see summarised at the bottom of the data list:

100 "Sumproduct result"

200 "Sumproduct result"


Thanks


Phil

 

[Hui]

Phil


I would use a formula similar to:

=SUMPRODUCT(--(IF(A1="",1,Cost_Centre=A1))*(IF(B1="",1,Employee=B1))*(Employee_pct)*(Employee_Effort))

Ctrl+Shift+Enter


Where your Cost Centre entry is in A1 and Employee entry is in B1

If A1 or B1 are blank the value is substituted by 1, meaning all


You can see this working here:

https://www.dropbox.com/s/761nebxn7xgxjzi/Phil.xlsx

 

[Harry Covair]

Hi Phil,


A suggestion, why don't use a pivot table with a calculated field "employee%*employee_effort". It should give:

Row Labels Sum of Field1

100 50%

200 94%


Hope it can help


Harry

 

[Phil]

Thankyou Hui and Harry for your comments.


Harry - I do like pivot tables for summarising data but have found it a bit inflexible when it comes to reporting. I've begun to move away from pivot tables, and am using the Sumproduct formula more.


Phil

 

[rahulsharma012]

Hi Phil,


Here is your simple solution to your query.


sending you as attachment


http://www.fileconvoy.com/dfl.php?id=ga43765ba53ae24a1999302507c60d19ebd79d3470


Regards,

Rahul