# Thread: Extracting info using formula (2002)

1. ## Extracting info using formula (2002)

Ok, I'm back to working on my waste report. My manager likes what I have done so far and now is asking me to summarize waste by the last 3 mo and 12 mo.

I have one tab for each product that we produce.
Column A Column B Column C Column D Column E
Date produced input quantity output quantity yield %waste

I know that sumif is for one criteria such as greater than November '06. How would I sum the input quantities for October '06 thru December '06?

Once I get the information, I can then transfer it to my summary page and chart it. I just don't know the best way. Could I use a pivot table for this? the data I want is spread out over 12 tabs (1 tab per product).

2. ## Re: Extracting info using formula (2002)

You can use SUMPRODUCT to get the sum. Let's say that the data are in rows 2 through 1000 (row 1 is probably a header row). The formula

=SUMPRODUCT((A2:A1000>=DATE(2006,10,1))*(A2:A1000< =DATE(2006,12,31))*B2:B1000)

should sum the entries in column B for which column A is between 1 October 2006 and 31 December 2006 (inclusive)

3. ## Re: Extracting info using formula (2002)

after a bit more digging, I found a formula like this that seems to work, too:

=SUMPRODUCT(--(\$A\$4:\$A\$26>=\$J\$35),--(\$A\$4:\$A\$26<=\$K\$35),B4:B26)

by removing the b4:26 portion, the formula counts the number of occurances as well.

I'll play around with yours this afternoon or tomorrow.

Thanks again Hans!

4. ## Re: Extracting info using formula (2002)

That's basically the same idea, you can use either.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•