| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT formula to include additonal range
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT formula to include additonal range
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT formula to include additonal range
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT formula to include additonal range
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT formula to include additonal range
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT formula to include additonal range
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT formula to include additonal range
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT formula to include additonal range
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT formula to include additonal range
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I include only visible cells in a range? | Excel Worksheet Functions | |||
| include INDIRECT function into SUMPRODUCT formula | Excel Worksheet Functions | |||
| IF with additonal Functions | Excel Worksheet Functions | |||
| Changing a range of an array in a SUMPRODUCT formula gives a #N/A error | Excel Discussion (Misc queries) | |||
| sum a range of cells that include an error | Excel Discussion (Misc queries) | |||
Linear Mode
