Selecting data from a table based upon two conditions

Have you tried ussing the search assistant (If you don't see it in the tools menu you need to install it, it's an AddIn), it'll guide you to the thing you're trying to do.

Juan Pablo

Don't seem to have this, is it in Excel 97? Also, i need this process written in a formula that references this table from another sheet. Will this be possible using the Seach Assistant you mentioned?

thanks

Do this:

First go to Tools, AddIns.

From there look for something like "Search Assistant", i don't have the exact name, but it's something like that.

Activate it if not activated.

Now, go again to Tools, Assistants, and select the search assistant. It'll show a series of steps that will guide you and will result in a formula, so you can change parameters and be able to recalculate.

And yes, it's in Excel 97 (I'm using it)

Juan Pablo Don't seem to have this, is it in Excel 97? Also, i need this process written in a formula that references this table from another sheet. Will this be possible using the Seach Assistant you mentioned? thanks

Thanks Juan,

Sounds perfect but the Add-in doesn't come standard with the Excel installation that our IT dept has set up, and is not available from the list of add-ins. Hopefully our IT dept will be able to help.

Mike

Ok then...

try with this formula. I assumed your data is in A1:F200

Put in H1 "CODE", I1 "MARKET" and J1 "SQTY"

Now in H2 put one for your codes, for example: 140864083

In I2 one of your markets. "Home"

Now in J2 put this formula

=SUMAPRODUCTO(($A$2:$A$200=H2)*($D$2:$D$200=I2)*($E$2:$E$200))

To change this formula to retrieve SVAL, change E2:E200 to F2:F200

Juan Pablo

Check this article out:
http://www.elementkjournals.com/ime/0109/ime0191.htm

Re: Ok then...

thanks very much, i owe you a drink.

Re: Ok then...

OK, just one more variation. What if the same CODE and MARKET exist exist more than once on particular rows, and I just want to extract SQTY from the first instance.

Mike

Re: Ok then...

Ok Mike, try this one...

Still in H2 and I2 are your conditions. In J2 put

=INDIRECT("E"&MATCH(H2&I2,$A$2:$A$200&$D$2:$D$200,0)

To enter this formula you should press Control Shift Enter at the same time.

To get the value of SVAL change "E" to "F" and that's it !

Juan Pablo OK, just one more variation. What if the same CODE and MARKET exist exist more than once on particular rows, and I just want to extract SQTY from the first instance. Mike

Have you tried using the DSum Function ?

Structure of Function:-
=DSUM(DatabaseRange,"MeasureToSum",Criteria Range)

The First range should cover all your data, including column titles (i.e Code,Date,Descr).

You then need to specify what measure you wish to sum (in your example this would be SQTY). Use speech marks around this measure.

Finally you need to specify the criteria range. For your query this would look like this:-

Code Market
X Y

Assuming the first range had been named TABLE1 and the Criteria range had the name Output1, then the formula would be as follows:-

=DSum(Table1,"SQTY",Output1)

This would sum SQTY where Code=X and Market=Y
To sum SVal in a similar way simply substitude "SQTY" for "SVal" in the formula.

Excel help gives further examples, and may be easier to follow than my brief note.