Hi again,

I am not sure I explained it right the first time, the formula below is
supposed to take column L into account and either take the full amount in L
column or 50 % of it depending on the E column, if the "resolved" is there or
the "closed" and also the J column if that is less than or equal to etc. But
one thing is missing, and that is that I want to add a third criteria, "in
progress", how do I do that?

=SUMPRODUCT(('Jira Reference'!$E5:$E1000="resolved")*('Jira
Reference'!$J5:$J1000<=B5)*('Jira
Reference'!$L5:$L1000))/2/3600+SUMPRODUCT(('Jira
Reference'!$E5:$E1000="closed")*('Jira Reference'!$J5:$J1000<=B5)*('Jira
Reference'!$L5:$L1000))/3600
--
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