I have a large range of data that I am working with and I would like to be
able to sum a colum based on two variables I have been trying to use sumif
and dsum but don't really have the answer I am looking for, any suggestions?
--
Jeff

Hi!

Do you mean that you have 2 variables in the same column?

If so, try one of these:

=SUMIF(A1:A10,variable_1,B1:B10)+SUMIF(A1:A10,vari able_2,B1:B10)

=SUMPRODUCT(--(A1:A10=variable_1)+(A1:A10=variable_2),B1:B10)

If the variables are in different columns:

=SUMPRODUCT(--(A1:A10=variable_1),--(B1:B10=variable_2),C1:C10)

In all of the above, if the variables are TEXT enclose them in quotes:
"Green"

If the variables are numeric do not use the quotes: 10

Biff

"Jeff" wrote in message
...
I have a large range of data that I am working with and I would like to be
able to sum a colum based on two variables I have been trying to use sumif
and dsum but don't really have the answer I am looking for, any
suggestions?
--
Jeff

No,
probably need to be a little more specific,

I have data in one column I would like to sum based on test values in two
other columns.

Column A= Owner "Text"
Column B = Hours "number"
Column C = "Status"


I would like to get a sum of hours based on the Owner and status.

Example Bob has 5 hours with status pending.
--
Jeff


"Biff" wrote:

Hi!

Do you mean that you have 2 variables in the same column?

If so, try one of these:

=SUMIF(A1:A10,variable_1,B1:B10)+SUMIF(A1:A10,vari able_2,B1:B10)

=SUMPRODUCT(--(A1:A10=variable_1)+(A1:A10=variable_2),B1:B10)

If the variables are in different columns:

=SUMPRODUCT(--(A1:A10=variable_1),--(B1:B10=variable_2),C1:C10)

In all of the above, if the variables are TEXT enclose them in quotes:
"Green"

If the variables are numeric do not use the quotes: 10

Biff

"Jeff" wrote in message
...
I have a large range of data that I am working with and I would like to be
able to sum a colum based on two variables I have been trying to use sumif
and dsum but don't really have the answer I am looking for, any
suggestions?
--
Jeff

=SUMPRODUCT((a1:a99="Bob")*(c1:c99="pending")*(b1: b99))

but use your own row ranges, of course.

- DH

Still Not working, for some reason I am getting #N/A

Does it matter if one of the selecting Columns is derived? by that I mean
the following,

=SUMPRODUCT((Team="Dev")*(Sprint!G3:G365="Pending" )*(Sprint!J3:J365))

Where "Pending" G3:G365, is determined by a formula. it could be complete or
in progress.
--
Jeff


"D Hilberg" wrote:

=SUMPRODUCT((a1:a99="Bob")*(c1:c99="pending")*(b1: b99))

but use your own row ranges, of course.

- DH

Hi!

If the variables are in different columns:

=SUMPRODUCT(--(A1:A10=variable_1),--(B1:B10=variable_2),C1:C10)

The above formula is the same as:

=SUMPRODUCT((Team="Dev")*(Sprint!G3:G365="Pending" )*(Sprint!J3:J365))

but is more efficient!

Does it matter if one of the selecting Columns is derived? by that I mean
the following,
Where "Pending" G3:G365, is determined by a formula. it could be complete
or
in progress.

No, it "shouldn't" matter. What does the defined name "TEAM" refer to? All
ranges must be EXACTLY the same size.

Biff

"Jeff" wrote in message
...
Still Not working, for some reason I am getting #N/A

Does it matter if one of the selecting Columns is derived? by that I mean
the following,

=SUMPRODUCT((Team="Dev")*(Sprint!G3:G365="Pending" )*(Sprint!J3:J365))

Where "Pending" G3:G365, is determined by a formula. it could be complete
or
in progress.
--
Jeff


"D Hilberg" wrote:

=SUMPRODUCT((a1:a99="Bob")*(c1:c99="pending")*(b1: b99))

but use your own row ranges, of course.

- DH

Just out of curiosity what does the -- signify/do?

ok so I set up the formula as suggested

=SUMPRODUCT(--(Team="Dev"),--(Status="Pending"),Sprint!J3:J365)

Where
Team =Sprint!B3:B365
Status =Sprint!G3:G365

=(INDEX(Burndown,ROW(G170)-ROW(G$2),MIN(TODAY()-SprintStart,29)*3+3))

which is in turn derived from

=IF(SUMPRODUCT($I$368:M$368,$I170:M170)<0.5, "Pending", IF(M170<0.5,
"Complete", "In Progress"))




and the data I am trying to sum is Sprint!J3:J365

and I am getting a #Value Error.

Thanks for the info Team refers to the team doing the work Team A or Team B
the formula that determins the status is
--
Jeff


"Biff" wrote:

Hi!

If the variables are in different columns:

=SUMPRODUCT(--(A1:A10=variable_1),--(B1:B10=variable_2),C1:C10)

The above formula is the same as:

=SUMPRODUCT((Team="Dev")*(Sprint!G3:G365="Pending" )*(Sprint!J3:J365))

but is more efficient!

Does it matter if one of the selecting Columns is derived? by that I mean
the following,
Where "Pending" G3:G365, is determined by a formula. it could be complete
or
in progress.

No, it "shouldn't" matter. What does the defined name "TEAM" refer to? All
ranges must be EXACTLY the same size.

Biff

"Jeff" wrote in message
...
Still Not working, for some reason I am getting #N/A

Does it matter if one of the selecting Columns is derived? by that I mean
the following,

=SUMPRODUCT((Team="Dev")*(Sprint!G3:G365="Pending" )*(Sprint!J3:J365))

Where "Pending" G3:G365, is determined by a formula. it could be complete
or
in progress.
--
Jeff


"D Hilberg" wrote:

=SUMPRODUCT((a1:a99="Bob")*(c1:c99="pending")*(b1: b99))

but use your own row ranges, of course.

- DH

Hi!

Just out of curiosity what does the -- signify/do?

See this:

http://mcgimpsey.com/excel/formulae/doubleneg.html

As far as your formulas go, they seem correct. You do have an extra set of
( ) in the Index formula but they're just being ignored. Are there any error
values ( #N/A, #VALUE!, #NUM!, etc.) in any of the ranges? They will cause
the problem your experiencing.

Biff

"Jeff" wrote in message
...
Just out of curiosity what does the -- signify/do?

ok so I set up the formula as suggested

=SUMPRODUCT(--(Team="Dev"),--(Status="Pending"),Sprint!J3:J365)

Where
Team =Sprint!B3:B365
Status =Sprint!G3:G365

=(INDEX(Burndown,ROW(G170)-ROW(G$2),MIN(TODAY()-SprintStart,29)*3+3))

which is in turn derived from

=IF(SUMPRODUCT($I$368:M$368,$I170:M170)<0.5, "Pending", IF(M170<0.5,
"Complete", "In Progress"))




and the data I am trying to sum is Sprint!J3:J365

and I am getting a #Value Error.

Thanks for the info Team refers to the team doing the work Team A or Team
B
the formula that determins the status is
--
Jeff


"Biff" wrote:

Hi!

If the variables are in different columns:

=SUMPRODUCT(--(A1:A10=variable_1),--(B1:B10=variable_2),C1:C10)

The above formula is the same as:

=SUMPRODUCT((Team="Dev")*(Sprint!G3:G365="Pending" )*(Sprint!J3:J365))

but is more efficient!

Does it matter if one of the selecting Columns is derived? by that I
mean
the following,
Where "Pending" G3:G365, is determined by a formula. it could be
complete
or
in progress.

No, it "shouldn't" matter. What does the defined name "TEAM" refer to?
All
ranges must be EXACTLY the same size.

Biff

"Jeff" wrote in message
...
Still Not working, for some reason I am getting #N/A

Does it matter if one of the selecting Columns is derived? by that I
mean
the following,

=SUMPRODUCT((Team="Dev")*(Sprint!G3:G365="Pending" )*(Sprint!J3:J365))

Where "Pending" G3:G365, is determined by a formula. it could be
complete
or
in progress.
--
Jeff


"D Hilberg" wrote:

=SUMPRODUCT((a1:a99="Bob")*(c1:c99="pending")*(b1: b99))

but use your own row ranges, of course.

- DH