Return result only if data complies.

[Guest]

Hi, I am trying to do some analysis, what I need is to extract data from a
file, but only the data that meets criteria, example, I want to extract data
from a file that shows orders with their respective delivery dates, I want to
extract the data that meets the following
Sheet 1
A B C
1 Product Qty Ship Date
2 Blue Pens 10 10/11/2005
3 Black pens 25 31/10/2005
4 Blue Pens 50 28/10/2005
5 Red pens 20 01/11/2005
6 Blue Pens 25 27/10/2005

In another tab I want to return the data that has already passed its Ship
Date, so the answer to the above would be. In col A I want the data to return
the product that is passed it's Ship date and in col B I want the data to
return the sum of those products that are passed their Ship date.

Sheet 2
A B
1 Blue Pens 75
2 Black pens 25
3 Red Pens 20

 

[Bob Phillips]

I would just list the products in column A, and then use

=SUMPRODUCT(--(Sheet1!A2:A200=A1),--(Sheet1!C2:C200<TODAY()),Sheet1!B2:B200)

it will show zeroes as well, but you could use conditional formatting to
highlight the ones that are not zero

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Bob, not quite what I need, I was already using SUMPRODUCT, but wondered
if there was a way to return ONLY the data that was passed its Ship date for
both the product and the sum of the qty.

 

[Dave Peterson]

How about an alternative?

Add another column to your data:
Past Ship
=c2>today()
and drag down.

You'll end up with a column of Trues/falses.

Then create a pivottable based on your data.

Select the range.
data|pivottable
follow the wizard until you get to the dialog with a layout button on it.
Click that layout button
Drag the past ship button to the page field
drag the product button to the row field
drag the qty button to the data field
(if it says "count of qty", double click on it and change it to Sum.)

Finish up.

On the page field, you can choose True/False or All to see your report.

If your list grows, you may want to create a dynamic range so that you don't
have to rebuild the pivottable each time it grows/contracts--you can just
rightclick on the pivottable and choose refresh instead.

See Debra Dalgeish's site for some nice tips:
http://contextures.com/xlNames01.html#Dynamic