Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2005
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    New Lounger
    Join Date
    Jun 2005
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •