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

J

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

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
 
J

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

D Hilberg

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

but use your own row ranges, of course.

- DH
 
J

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

Biff

Hi!
If the variables are in different columns:
Click to expand...
=SUMPRODUCT(--(A1:A10=variable_1),--(B1:B10=variable_2),C1:C10)
Click to expand...

The above formula is the same as:
=SUMPRODUCT((Team="Dev")*(Sprint!G3:G365="Pending")*(Sprint!J3:J365))
Click to expand...

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.
Click to expand...

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

Biff
 
J

Jeff

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
 
Top