Add several functions in one formula

[Brile]

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%.

 

[Pete_UK]

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

 

[Brile]

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

 

[Pete_UK]

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

 

[Brile]

Hi,

actually it does not add the numbers...Do you have another idea on it?

thankful for your help, by the way

 

[Pete_UK]

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

 

[Brile]

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]

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?

 

[Pete_UK]

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]

Yeah! With other words thanks, have a great day and perhaps se you again
on the forum