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 -