Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summing with conditions from 2 lists (2000)

    I Have 3 lists one contains job codes, one contains dates the jobs were done on and the third list contains costs, now i can have seperate costs apportioned to the same job on the same day what i need to be able to do is find the same job codes that match a particular day and sum costs to create a total cost. now i need to do this without a filter inconjuction with the SUBTOTAL function. Is this possible...... <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Summing with conditions from 2 lists (2000)

    I don't understand your setup or what you want. Could you explain it in some more detail?

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing with conditions from 2 lists (2000)

    Could you upload a sample workbook that shows what you want to do? Your explanation is a little too ambiguous.
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing with conditions from 2 lists (2000)

    Sounds like SUMPRODUCT:

    =SUMPRODUCT(($A$1:$A$20="Job Code")*($B$1:$B$20="Date"),$C$1:$C$20)

    Put in the Job code and date as necessary (if they are formatted as numbers then omit the " marks), and this will give you the total for each Job code on a specific day.

  5. #5
    New Lounger
    Join Date
    Jan 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing with conditions from 2 lists (2000)

    Thanks for replying. EG 3 lists below. i need to match all the dates that match the job code FIL478 on 18-Jan-04 and sum the relative expenses. in the case of this list the value to be returned would be

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Summing with conditions from 2 lists (2000)

    1) You don't need to put duplicate responses to people in the same thread. Just respond once and we will all be able to see it.

    2) Doesn't the response from <!profile=shades>shades<!/profile> in <post#=334847>post 334847</post#> answer your question?

    3) if you want to get a summary of the dates and job codes you should investigate a pivot table report which can give a summary by date and job code of this type of data.

    Steve

  7. #7
    New Lounger
    Join Date
    Jan 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing with conditions from 2 lists (2000)

    Thanx with some tweaking that has worked. Any chance you could explain how it works <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Summing with conditions from 2 lists (2000)

    SumProduct is a builtin function in excel that "innately handles" ARRAYs (as opposed to excel "Array formulas" that you must tell excel are arrays, by using ctrl-shift-enter) Both work similarly. The main diference is confirming with <enter> of ctrl-shift-enter.

    For a "primer" on array formulas and their uses check out Chip Pearson's excellent site and also Bob Umlas' "paper"
    The formula:
    <pre>=SUMPRODUCT(($A$1:$A$20="Job Code")*($B$1:$B$20="Date"),$C$1:$C$20)</pre>

    Can be broken into parts:
    "Internally" it creates several 20 member arrays (1 col, 20 rows)
    Array 1 is a 20 member array of TRUEs and FALSEs.
    Item 1) A1="Job Code"
    Item 2) A2= "Job Code"
    ...
    Item 20) A20= "Job Code"

    Array 2 is another 20 member (1 col, 20 rows) array of TRUEs and FALSEs.
    Item 1) B1="Date"
    Item 2) B2="Date"
    ...
    Item 20) B20="Date"

    Array 3 is another 20 member (1 col, 20 rows) array of values
    Item 1) value in C1
    Item 2) value in C2
    ...
    Item 20) value in C20

    These would be equivalent to:
    Array 1 putting the formula in D1: =A1="Job Code" and copying it from A2:A20
    Array 2 putting the formula in E1: =B1="Date" and copying it from B2:B20
    Array 3 is equivalent to C1:C20

    The next part is multiplication of Array 1 and 2 [($A$1:$A$20="Job Code")*($B$1:$B$20="Date")]
    This creates (internally) another array (1 col, 20 rows) of either 1s or 0s
    When you ask excel to work with TRUE/FALSE values in mathematical expressions, a False = 0, and True = 1, so this multiplication is like an "AND": to get TRUE both items must be TRUE (1*1) any one with at least 1 false will be False(1*0, 0*1, 0*0)

    Array 4
    Item 1) If Both item 1s in A and B are TRUE, then True else False
    Item 2) If Both item 2s in A and B are TRUE, then True else False
    ...
    Item 20) If Both item 20s in A and B are TRUE, then True else False

    This would be equivalent to creating in Col F, in F1 <pre>=D1*E1</pre>

    and copying it to F2:F20
    The last is multiplication of Array 4 with Array 3 in a similar manner to yield array 5 which will be either a value from C or zero

    In Col G, G1 created <pre>=C1*F1</pre>

    and copy from G2:G20

    Then sum Array 5 (or create the formula [pre]=sum(G1:G20)

    Ultimately, it means that if both the condition comparing A to Job code is true, AND the condition that the B = DATE is true, then it will result in the value in C, if either is false it will be zero. So when excel sums these 20 values it will sum only the ones matching the criteria.

    You can do this with intermediate columns (as I described), or using arrays, can let excel do it internally.

    Steve

Posting Permissions

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