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

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

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

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 -

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 -

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 -