# SUMPRODUCT formula to include additonal range

B

#### 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

B

#### Bernard Liengme

=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

B

#### 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?

B

#### 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

B

#### 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

B

#### 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

B

#### Bernard Liengme

Send me (my private email) a sample file

B

#### 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?