Results 1 to 8 of 8

20040122, 23:30 #1
 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>

20040122, 23:54 #2
 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

20040122, 23:56 #3
 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

20040123, 13:33 #4
 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.

20040124, 12:59 #5
 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 18Jan04 and sum the relative expenses. in the case of this list the value to be returned would be

20040124, 15:01 #6
 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

20040124, 15:34 #7
 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>

20040125, 10:55 #8
 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 ctrlshiftenter) Both work similarly. The main diference is confirming with <enter> of ctrlshiftenter.
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