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

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

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

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

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

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" <Jeff@discussions.microsoft.com> wrote in message
news:B0FEC5B0-4258-4435-B6A6-E9B15085AE03@microsoft.com...
> 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" <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
>> >>
>> >>
>>
>>
>>