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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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.
 
=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.
 
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
 
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
 
Back
Top