Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add several functions in one formula
Hi,
If I already have a "SUMIF" formula (see below) which returns a certain value and I would like Excel to add to that formula an additional function that adds certain data from an additional column in a tab - where and how do I do that in the formula? The thing I would like to do is: Add data from a column but only all the rows with a special value in that column, for example with the string "Open". =SUMIF('Jira Reference'!$J:$J;"<="&C5;'Jira Reference'!$L:$L)/3600 I also to that formula would like to add the value of those same rows that says "Open" but from another additional column and also multiplicate that with 50%. -- Brile |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add several functions in one formula
Well, can't you add this to your existing formula:
+SUMIF('Jira Reference'!$H:$H;"open";'Jira Reference'!$L:$L)/2 assuming the values you are looking for are in column H - adjust to suit your data. Hope ths helps. Pete On Oct 3, 12:05*pm, Brile wrote: Hi, If I already have a "SUMIF" formula (see below) which returns a certain value and I would like Excel to add to that formula an additional function that adds certain data from an additional column in a tab - where and how do I do that in the formula? The thing I would like to do is: Add data from a column but only all the rows with a special value in that column, for example with the string "Open". =SUMIF('Jira Reference'!$J:$J;"<="&C5;'Jira Reference'!$L:$L)/3600 I also to that formula would like to add the value of those same rows that says "Open" but from another additional column and also multiplicate that with 50%. -- Brile |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add several functions in one formula
Hi again and thanks for the reply,
What does the formula below tell you? =SUMIF('Jira Reference'!$K:$K;"closed""<="&G5;'Jira Reference'!$L:$L)/3600+SUMIF('Jira Reference'!$E:$E;"open";'Jira Reference'!$L:$L)/2/3600 Now, with your solution it actually counts the added function but not the original one...? Do you know why by looking at the formula or do I need to send you something else? The formula should take into account the "open" times 50% and the "closed" times 100% (the closed are the first function in the formula). This should add up to 248 + 69 (Closed and Open) but only now counts the 69??? -- Brile "Pete_UK" wrote: Well, can't you add this to your existing formula: +SUMIF('Jira Reference'!$H:$H;"open";'Jira Reference'!$L:$L)/2 assuming the values you are looking for are in column H - adjust to suit your data. Hope ths helps. Pete On Oct 3, 12:05 pm, Brile wrote: Hi, If I already have a "SUMIF" formula (see below) which returns a certain value and I would like Excel to add to that formula an additional function that adds certain data from an additional column in a tab - where and how do I do that in the formula? The thing I would like to do is: Add data from a column but only all the rows with a special value in that column, for example with the string "Open". =SUMIF('Jira Reference'!$J:$J;"<="&C5;'Jira Reference'!$L:$L)/3600 I also to that formula would like to add the value of those same rows that says "Open" but from another additional column and also multiplicate that with 50%. -- Brile |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add several functions in one formula
The first part of it is incorrect - you can only have one condition in
SUMIF, so I think you want it to be: =SUMIF('Jira Reference'!$K:$K;"closed";'Jira Reference'!$L:$L)/ 3600+SUMIF('Jira Reference'!$E:$E;"open";'Jira Reference'!$L:$L)/ 2/3600 Hope this helps. Pete On Oct 3, 3:56*pm, Brile wrote: Hi again and thanks for the reply, What does the formula below tell you? =SUMIF('Jira Reference'!$K:$K;"closed""<="&G5;'Jira Reference'!$L:$L)/3600+SUMIF('Jira Reference'!$E:$E;"open";'Jira Reference'!$L:$L)/2/3600 Now, with your solution it actually counts the added function but not the original one...? Do you know why by looking at the formula or do I need to send you something else? The formula should take into account the "open" times 50% and the "closed" times 100% (the closed are the first function in the formula). This should add up to 248 + 69 (Closed and Open) but only now counts the 69??? -- Brile "Pete_UK" wrote: Well, can't you add this to your existing formula: +SUMIF('Jira Reference'!$H:$H;"open";'Jira Reference'!$L:$L)/2 assuming the values you are looking for are in column H - adjust to suit your data. Hope ths helps. Pete On Oct 3, 12:05 pm, Brile wrote: Hi, If I already have a "SUMIF" formula (see below) which returns a certain value and I would like Excel to add to that formula an additional function that adds certain data from an additional column in a tab - where and how do I do that in the formula? The thing I would like to do is: Add data from a column but only all the rows with a special value in that column, for example with the string "Open". =SUMIF('Jira Reference'!$J:$J;"<="&C5;'Jira Reference'!$L:$L)/3600 I also to that formula would like to add the value of those same rows that says "Open" but from another additional column and also multiplicate that with 50%. -- Brile- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add several functions in one formula
Hi,
actually it does not add the numbers...Do you have another idea on it? thankful for your help, by the way :-) -- Brile "Pete_UK" wrote: The first part of it is incorrect - you can only have one condition in SUMIF, so I think you want it to be: =SUMIF('Jira Reference'!$K:$K;"closed";'Jira Reference'!$L:$L)/ 3600+SUMIF('Jira Reference'!$E:$E;"open";'Jira Reference'!$L:$L)/ 2/3600 Hope this helps. Pete On Oct 3, 3:56 pm, Brile wrote: Hi again and thanks for the reply, What does the formula below tell you? =SUMIF('Jira Reference'!$K:$K;"closed""<="&G5;'Jira Reference'!$L:$L)/3600+SUMIF('Jira Reference'!$E:$E;"open";'Jira Reference'!$L:$L)/2/3600 Now, with your solution it actually counts the added function but not the original one...? Do you know why by looking at the formula or do I need to send you something else? The formula should take into account the "open" times 50% and the "closed" times 100% (the closed are the first function in the formula). This should add up to 248 + 69 (Closed and Open) but only now counts the 69??? -- Brile "Pete_UK" wrote: Well, can't you add this to your existing formula: +SUMIF('Jira Reference'!$H:$H;"open";'Jira Reference'!$L:$L)/2 assuming the values you are looking for are in column H - adjust to suit your data. Hope ths helps. Pete On Oct 3, 12:05 pm, Brile wrote: Hi, If I already have a "SUMIF" formula (see below) which returns a certain value and I would like Excel to add to that formula an additional function that adds certain data from an additional column in a tab - where and how do I do that in the formula? The thing I would like to do is: Add data from a column but only all the rows with a special value in that column, for example with the string "Open". =SUMIF('Jira Reference'!$J:$J;"<="&C5;'Jira Reference'!$L:$L)/3600 I also to that formula would like to add the value of those same rows that says "Open" but from another additional column and also multiplicate that with 50%. -- Brile- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add several functions in one formula
Well the first formula you quoted is different to what you have now,
so perhaps you want: =SUMIF('Jira Reference'!$K:$K;"<="&C5;'Jira Reference'!$L:$L)/ 3600+SUMIF('Jira Reference'!$E:$E;"open";'Jira Reference'!$L:$L)/ 2/3600 although maybe C5 contained the word closed. Pete On Oct 3, 4:24*pm, Brile wrote: Hi, actually it does not add the numbers...Do you have another idea on it? thankful for your help, by the way :-) -- Brile "Pete_UK" wrote: The first part of it is incorrect - you can only have one condition in SUMIF, so I think you want it to be: =SUMIF('Jira Reference'!$K:$K;"closed";'Jira Reference'!$L:$L)/ 3600+SUMIF('Jira Reference'!$E:$E;"open";'Jira Reference'!$L:$L)/ 2/3600 Hope this helps. Pete On Oct 3, 3:56 pm, Brile wrote: Hi again and thanks for the reply, What does the formula below tell you? =SUMIF('Jira Reference'!$K:$K;"closed""<="&G5;'Jira Reference'!$L:$L)/3600+SUMIF('Jira Reference'!$E:$E;"open";'Jira Reference'!$L:$L)/2/3600 Now, with your solution it actually counts the added function but not the original one...? Do you know why by looking at the formula or do I need to send you something else? The formula should take into account the "open" times 50% and the "closed" times 100% (the closed are the first function in the formula). This should add up to 248 + 69 (Closed and Open) but only now counts the 69??? -- Brile "Pete_UK" wrote: Well, can't you add this to your existing formula: +SUMIF('Jira Reference'!$H:$H;"open";'Jira Reference'!$L:$L)/2 assuming the values you are looking for are in column H - adjust to suit your data. Hope ths helps. Pete On Oct 3, 12:05 pm, Brile wrote: Hi, If I already have a "SUMIF" formula (see below) which returns a certain value and I would like Excel to add to that formula an additional function that adds certain data from an additional column in a tab - where and how do I do that in the formula? The thing I would like to do is: Add data from a column but only all the rows with a special value in that column, for example with the string "Open". =SUMIF('Jira Reference'!$J:$J;"<="&C5;'Jira Reference'!$L:$L)/3600 I also to that formula would like to add the value of those same rows that says "Open" but from another additional column and also multiplicate that with 50%. -- Brile- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add several functions in one formula
Hi again,
I have tried all morning but nothing works, can we give it one more try?... This is the formula: =SUMIF('Jira Reference'!$K:$K;"<="&G5;'Jira Reference'!$L:$L)/3600+SUMIF('Jira Reference'!$E:$K;"=open""<="&G5;'Jira Reference'!$L:$L)/2/3600 What I would like for it to do is: Retrive data if the data in Jira Reference tab column K is <= the cell G5 in the start tab (or any cell at that row, marks with "&G5"). If that criteria matches Excel should take the data from column L in the Jira Reference tab and also divide it by 3600 (the sum in the L column is seconds and to get hours I divide it by 3600). Then I would like to add an additional function, this time I would like Excel to check if data in column E in the Jira Reference tab equals "open" and is <= the column K (as above in the text), if it does, Excel should add the data from the L column in Jira Reference tab and divide by 2 (50%). Any new suggestions? Thanks for your help yesterday by the way, -- Brile "Pete_UK" wrote: Well the first formula you quoted is different to what you have now, so perhaps you want: =SUMIF('Jira Reference'!$K:$K;"<="&C5;'Jira Reference'!$L:$L)/ 3600+SUMIF('Jira Reference'!$E:$E;"open";'Jira Reference'!$L:$L)/ 2/3600 although maybe C5 contained the word closed. Pete On Oct 3, 4:24 pm, Brile wrote: Hi, actually it does not add the numbers...Do you have another idea on it? thankful for your help, by the way :-) -- Brile "Pete_UK" wrote: The first part of it is incorrect - you can only have one condition in SUMIF, so I think you want it to be: =SUMIF('Jira Reference'!$K:$K;"closed";'Jira Reference'!$L:$L)/ 3600+SUMIF('Jira Reference'!$E:$E;"open";'Jira Reference'!$L:$L)/ 2/3600 Hope this helps. Pete On Oct 3, 3:56 pm, Brile wrote: Hi again and thanks for the reply, What does the formula below tell you? =SUMIF('Jira Reference'!$K:$K;"closed""<="&G5;'Jira Reference'!$L:$L)/3600+SUMIF('Jira Reference'!$E:$E;"open";'Jira Reference'!$L:$L)/2/3600 Now, with your solution it actually counts the added function but not the original one...? Do you know why by looking at the formula or do I need to send you something else? The formula should take into account the "open" times 50% and the "closed" times 100% (the closed are the first function in the formula). This should add up to 248 + 69 (Closed and Open) but only now counts the 69??? -- Brile "Pete_UK" wrote: Well, can't you add this to your existing formula: +SUMIF('Jira Reference'!$H:$H;"open";'Jira Reference'!$L:$L)/2 assuming the values you are looking for are in column H - adjust to suit your data. Hope ths helps. Pete On Oct 3, 12:05 pm, Brile wrote: Hi, If I already have a "SUMIF" formula (see below) which returns a certain value and I would like Excel to add to that formula an additional function that adds certain data from an additional column in a tab - where and how do I do that in the formula? The thing I would like to do is: Add data from a column but only all the rows with a special value in that column, for example with the string "Open". =SUMIF('Jira Reference'!$J:$J;"<="&C5;'Jira Reference'!$L:$L)/3600 I also to that formula would like to add the value of those same rows that says "Open" but from another additional column and also multiplicate that with 50%. -- Brile- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add several functions in one formula
I think I have solved it by using:
=SUMPRODUCT(('Jira Reference'!$E2:$E1000="open")*('Jira Reference'!$K2:$K1000<=C5)*('Jira Reference'!$L2:$L1000))/2/3600+SUMPRODUCT(('Jira Reference'!$E2:$E1000="closed")*('Jira Reference'!$K2:$K1000<=C5)*('Jira Reference'!$L2:$L1000))/3600 What do you think? -- Brile "Brile" wrote: Hi again, I have tried all morning but nothing works, can we give it one more try?... This is the formula: =SUMIF('Jira Reference'!$K:$K;"<="&G5;'Jira Reference'!$L:$L)/3600+SUMIF('Jira Reference'!$E:$K;"=open""<="&G5;'Jira Reference'!$L:$L)/2/3600 What I would like for it to do is: Retrive data if the data in Jira Reference tab column K is <= the cell G5 in the start tab (or any cell at that row, marks with "&G5"). If that criteria matches Excel should take the data from column L in the Jira Reference tab and also divide it by 3600 (the sum in the L column is seconds and to get hours I divide it by 3600). Then I would like to add an additional function, this time I would like Excel to check if data in column E in the Jira Reference tab equals "open" and is <= the column K (as above in the text), if it does, Excel should add the data from the L column in Jira Reference tab and divide by 2 (50%). Any new suggestions? Thanks for your help yesterday by the way, -- Brile "Pete_UK" wrote: Well the first formula you quoted is different to what you have now, so perhaps you want: =SUMIF('Jira Reference'!$K:$K;"<="&C5;'Jira Reference'!$L:$L)/ 3600+SUMIF('Jira Reference'!$E:$E;"open";'Jira Reference'!$L:$L)/ 2/3600 although maybe C5 contained the word closed. Pete On Oct 3, 4:24 pm, Brile wrote: Hi, actually it does not add the numbers...Do you have another idea on it? thankful for your help, by the way :-) -- Brile "Pete_UK" wrote: The first part of it is incorrect - you can only have one condition in SUMIF, so I think you want it to be: =SUMIF('Jira Reference'!$K:$K;"closed";'Jira Reference'!$L:$L)/ 3600+SUMIF('Jira Reference'!$E:$E;"open";'Jira Reference'!$L:$L)/ 2/3600 Hope this helps. Pete On Oct 3, 3:56 pm, Brile wrote: Hi again and thanks for the reply, What does the formula below tell you? =SUMIF('Jira Reference'!$K:$K;"closed""<="&G5;'Jira Reference'!$L:$L)/3600+SUMIF('Jira Reference'!$E:$E;"open";'Jira Reference'!$L:$L)/2/3600 Now, with your solution it actually counts the added function but not the original one...? Do you know why by looking at the formula or do I need to send you something else? The formula should take into account the "open" times 50% and the "closed" times 100% (the closed are the first function in the formula). This should add up to 248 + 69 (Closed and Open) but only now counts the 69??? -- Brile "Pete_UK" wrote: Well, can't you add this to your existing formula: +SUMIF('Jira Reference'!$H:$H;"open";'Jira Reference'!$L:$L)/2 assuming the values you are looking for are in column H - adjust to suit your data. Hope ths helps. Pete On Oct 3, 12:05 pm, Brile wrote: Hi, If I already have a "SUMIF" formula (see below) which returns a certain value and I would like Excel to add to that formula an additional function that adds certain data from an additional column in a tab - where and how do I do that in the formula? The thing I would like to do is: Add data from a column but only all the rows with a special value in that column, for example with the string "Open". =SUMIF('Jira Reference'!$J:$J;"<="&C5;'Jira Reference'!$L:$L)/3600 I also to that formula would like to add the value of those same rows that says "Open" but from another additional column and also multiplicate that with 50%. -- Brile- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add several functions in one formula
You can only use one condition for SUMIF, but your second requirement states
two conditions - you want column E to contain "open" AND column K to be less than G5. For this you will have to use SUMPRODUCT, but be aware that you cannot use full column references with this (unless you have XL2007) and the ranges must span the same number of rows. So, you might like to try this: =SUMIF('Jira Reference'!$K:$K;"<="&G5;'Jira Reference'!$L:$L)/3600 +SUMPRODUCT(('Jira Reference'!$E$1:$E$1000="open")*('Jira Reference'!$K$1:$K$1000<=G5)*('Jira Reference'!$L$1:$L$1000))/2/3600 I've assumed you are interested in rows 1 to 1000, so change the range references if you have more. Let me know how this one goes. Hope this helps. Pete "Brile" wrote in message ... Hi again, I have tried all morning but nothing works, can we give it one more try?... This is the formula: =SUMIF('Jira Reference'!$K:$K;"<="&G5;'Jira Reference'!$L:$L)/3600+SUMIF('Jira Reference'!$E:$K;"=open""<="&G5;'Jira Reference'!$L:$L)/2/3600 What I would like for it to do is: Retrive data if the data in Jira Reference tab column K is <= the cell G5 in the start tab (or any cell at that row, marks with "&G5"). If that criteria matches Excel should take the data from column L in the Jira Reference tab and also divide it by 3600 (the sum in the L column is seconds and to get hours I divide it by 3600). Then I would like to add an additional function, this time I would like Excel to check if data in column E in the Jira Reference tab equals "open" and is <= the column K (as above in the text), if it does, Excel should add the data from the L column in Jira Reference tab and divide by 2 (50%). Any new suggestions? Thanks for your help yesterday by the way, -- Brile "Pete_UK" wrote: Well the first formula you quoted is different to what you have now, so perhaps you want: =SUMIF('Jira Reference'!$K:$K;"<="&C5;'Jira Reference'!$L:$L)/ 3600+SUMIF('Jira Reference'!$E:$E;"open";'Jira Reference'!$L:$L)/ 2/3600 although maybe C5 contained the word closed. Pete On Oct 3, 4:24 pm, Brile wrote: Hi, actually it does not add the numbers...Do you have another idea on it? thankful for your help, by the way :-) -- Brile "Pete_UK" wrote: The first part of it is incorrect - you can only have one condition in SUMIF, so I think you want it to be: =SUMIF('Jira Reference'!$K:$K;"closed";'Jira Reference'!$L:$L)/ 3600+SUMIF('Jira Reference'!$E:$E;"open";'Jira Reference'!$L:$L)/ 2/3600 Hope this helps. Pete On Oct 3, 3:56 pm, Brile wrote: Hi again and thanks for the reply, What does the formula below tell you? =SUMIF('Jira Reference'!$K:$K;"closed""<="&G5;'Jira Reference'!$L:$L)/3600+SUMIF('Jira Reference'!$E:$E;"open";'Jira Reference'!$L:$L)/2/3600 Now, with your solution it actually counts the added function but not the original one...? Do you know why by looking at the formula or do I need to send you something else? The formula should take into account the "open" times 50% and the "closed" times 100% (the closed are the first function in the formula). This should add up to 248 + 69 (Closed and Open) but only now counts the 69??? -- Brile "Pete_UK" wrote: Well, can't you add this to your existing formula: +SUMIF('Jira Reference'!$H:$H;"open";'Jira Reference'!$L:$L)/2 assuming the values you are looking for are in column H - adjust to suit your data. Hope ths helps. Pete On Oct 3, 12:05 pm, Brile wrote: Hi, If I already have a "SUMIF" formula (see below) which returns a certain value and I would like Excel to add to that formula an additional function that adds certain data from an additional column in a tab - where and how do I do that in the formula? The thing I would like to do is: Add data from a column but only all the rows with a special value in that column, for example with the string "Open". =SUMIF('Jira Reference'!$J:$J;"<="&C5;'Jira Reference'!$L:$L)/3600 I also to that formula would like to add the value of those same rows that says "Open" but from another additional column and also multiplicate that with 50%. -- Brile- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add several functions in one formula
Yeah! :-) With other words thanks, have a great day and perhaps se you again
on the forum :-) -- Brile "Pete_UK" wrote: You can only use one condition for SUMIF, but your second requirement states two conditions - you want column E to contain "open" AND column K to be less than G5. For this you will have to use SUMPRODUCT, but be aware that you cannot use full column references with this (unless you have XL2007) and the ranges must span the same number of rows. So, you might like to try this: =SUMIF('Jira Reference'!$K:$K;"<="&G5;'Jira Reference'!$L:$L)/3600 +SUMPRODUCT(('Jira Reference'!$E$1:$E$1000="open")*('Jira Reference'!$K$1:$K$1000<=G5)*('Jira Reference'!$L$1:$L$1000))/2/3600 I've assumed you are interested in rows 1 to 1000, so change the range references if you have more. Let me know how this one goes. Hope this helps. Pete "Brile" wrote in message ... Hi again, I have tried all morning but nothing works, can we give it one more try?... This is the formula: =SUMIF('Jira Reference'!$K:$K;"<="&G5;'Jira Reference'!$L:$L)/3600+SUMIF('Jira Reference'!$E:$K;"=open""<="&G5;'Jira Reference'!$L:$L)/2/3600 What I would like for it to do is: Retrive data if the data in Jira Reference tab column K is <= the cell G5 in the start tab (or any cell at that row, marks with "&G5"). If that criteria matches Excel should take the data from column L in the Jira Reference tab and also divide it by 3600 (the sum in the L column is seconds and to get hours I divide it by 3600). Then I would like to add an additional function, this time I would like Excel to check if data in column E in the Jira Reference tab equals "open" and is <= the column K (as above in the text), if it does, Excel should add the data from the L column in Jira Reference tab and divide by 2 (50%). Any new suggestions? Thanks for your help yesterday by the way, -- Brile "Pete_UK" wrote: Well the first formula you quoted is different to what you have now, so perhaps you want: =SUMIF('Jira Reference'!$K:$K;"<="&C5;'Jira Reference'!$L:$L)/ 3600+SUMIF('Jira Reference'!$E:$E;"open";'Jira Reference'!$L:$L)/ 2/3600 although maybe C5 contained the word closed. Pete On Oct 3, 4:24 pm, Brile wrote: Hi, actually it does not add the numbers...Do you have another idea on it? thankful for your help, by the way :-) -- Brile "Pete_UK" wrote: The first part of it is incorrect - you can only have one condition in SUMIF, so I think you want it to be: =SUMIF('Jira Reference'!$K:$K;"closed";'Jira Reference'!$L:$L)/ 3600+SUMIF('Jira Reference'!$E:$E;"open";'Jira Reference'!$L:$L)/ 2/3600 Hope this helps. Pete On Oct 3, 3:56 pm, Brile wrote: Hi again and thanks for the reply, What does the formula below tell you? =SUMIF('Jira Reference'!$K:$K;"closed""<="&G5;'Jira Reference'!$L:$L)/3600+SUMIF('Jira Reference'!$E:$E;"open";'Jira Reference'!$L:$L)/2/3600 Now, with your solution it actually counts the added function but not the original one...? Do you know why by looking at the formula or do I need to send you something else? The formula should take into account the "open" times 50% and the "closed" times 100% (the closed are the first function in the formula). This should add up to 248 + 69 (Closed and Open) but only now counts the 69??? -- Brile "Pete_UK" wrote: Well, can't you add this to your existing formula: +SUMIF('Jira Reference'!$H:$H;"open";'Jira Reference'!$L:$L)/2 assuming the values you are looking for are in column H - adjust to suit your data. Hope ths helps. Pete On Oct 3, 12:05 pm, Brile wrote: Hi, If I already have a "SUMIF" formula (see below) which returns a certain value and I would like Excel to add to that formula an additional function that adds certain data from an additional column in a tab - where and how do I do that in the formula? The thing I would like to do is: Add data from a column but only all the rows with a special value in that column, for example with the string "Open". =SUMIF('Jira Reference'!$J:$J;"<="&C5;'Jira Reference'!$L:$L)/3600 I also to that formula would like to add the value of those same rows that says "Open" but from another additional column and also multiplicate that with 50%. -- Brile- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert cell formula functions to code functions | Excel Discussion (Misc queries) | |||
formula/functions for average and if functions | Excel Worksheet Functions | |||
If/Or Formula Functions | Excel Discussion (Misc queries) | |||
Using the AND and OR functions in the same formula | Excel Worksheet Functions | |||
efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) |