How do I select on two variables in a range of data in excel

[Jeff]

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

[Biff]

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,variable_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" <Jeff@discussions.microsoft.com> wrote in message
news:AED441BB-6028-45C3-A7F8-3DECE7E90A82@microsoft.com...
>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

[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,variable_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" <Jeff@discussions.microsoft.com> wrote in message
> news:AED441BB-6028-45C3-A7F8-3DECE7E90A82@microsoft.com...
> >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
>
>
>

[D Hilberg]

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

but use your own row ranges, of course.

- DH

[Jeff]

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

[Biff]

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" <Jeff@discussions.microsoft.com> wrote in message
news:EBAB4508-C3CC-43F6-8794-531816C210EC@microsoft.com...
> 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
>>
>>