visit my website
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Brile" wrote in message
...
How do I do that, I can not see you email and do not know how to add files
to
this web based program?

--
Brile


"Bernard Liengme" wrote:

Send me (my private email) a sample file
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Brile" wrote in message
...
Hi,

it does not calculate it right unfortunately...

I am refrasing myself,
If I would like to add also "in progress" as I have written "resolved"
in
the below formula, how would I do that? I have tried to use the
suggestions
but it does not work.


=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:

My first formula should work. I have removed the division by 2 below
=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
+
SUMPRODUCT(('Jira Reference'!$E5:$E1000="in progress")*
('Jira Reference'!$J5:$J1000<=C5)*('Jira Reference'!$L5:$L1000))/3600

or , doing the 3600 division in one operation:

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

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

"Brile" wrote in message
...
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