The /2 is to count the data as 50% and the /3600 is to divide the sum that is
in seconds and make it hours.

Still do not get if I should have the both suggestions from you, or should
they be putted after each other?

Thanks for your help,

--
Brile


"Bernard Liengme" wrote:

How about
=SUMPRODUCT(('Jira Reference'!$E5:$E1000="resolved")*
('Jira Reference'!$J5:$J1000<=C5)*('Jira Reference'!$L5:$L1000))/2/3600 +
SUMPRODUCT(('Jira Reference'!$E5:$E1000="closed")*
('Jira Reference'!$J5:$J1000<=C5)*('Jira Reference'!$L5:$L1000))/2/3600 +
SUMPRODUCT(('Jira Reference'!$E5:$E1000="in progress")*
('Jira Reference'!$J5:$J1000<=C5)*('Jira Reference'!$L5:$L1000))/2/3600 +

not sure what the /2/3600 is all about (!)

then try
=SUMPRODUCT( (('Jira Reference'!$E5:$E1000="resolved")+('Jira
Reference'!$E5:$E1000="closed") + ('Jira Reference'!$E5:$E1000="in
progress"))
* ('Jira Reference'!$J5:$J1000<=C5)*('Jira Reference'!$L5:$L1000))/2/3600

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Brile" wrote in message
...
How do I get the formula below to also include the value "in progress",
please see in the formula how I have written "resolved" and "closed". What
must I add to the formula in order to get it to also check the "in
progress
value"?

=SUMPRODUCT(('Jira Reference'!$E5:$E1000="resolved")*('Jira
Reference'!$J5:$J1000<=C5)*('Jira
Reference'!$L5:$L1000))/2/3600+SUMPRODUCT(('Jira
Reference'!$E5:$E1000="closed")*('Jira Reference'!$J5:$J1000<=C5)*('Jira
Reference'!$L5:$L1000))/3600
--
Brile