SUMPRODUCT formula to include additonal range

[Brile]

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

 

[Bernard Liengme]

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

 

[Brile]

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]

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

 

[Bernard Liengme]

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

 

[Brile]

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

 

[Bernard Liengme]

Send me (my private email) a sample file

 

[Brile]

How do I do that, I can not see you email and do not know how to add files to
this web based program?

 

[Bernard Liengme]

visit my website