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