software

how do I sum a column of data based on criteria in two other columns? I have
three columns of data, one contains account numbers, one dates and the third
data to be summed. The difficulty is that there are duplicate entries and
the account numbers go to 8 digits and I am summing to 6.

customer-project-part Hours Date
A B
C
1234-02-06 20
01/01/06
1234-02-07 20
01/01/06
1234-02-08 20
02/02/06

sum of 1234-02 =40

The account 1234-02-07 is ignored because it is on the same date as
1234-02-06. The situation is that I am adding the total number of hours a
machine is run but there are multiple parts going through at once. So I am
adding only one occurance per project number for each day.ThanksOne way

=SUMPRODUCT(--(C2:C10=--quot;2006-1-1quot;),--(LEFT(TRIM(A2:A10),7)=quot;1234-02quot;),B2:B10)
--

Regards,

Peo Sjoblom

nwexcelsolutions.com
quot;Toddquot; gt; wrote in message
...
gt; how do I sum a column of data based on criteria in two other columns? I
gt; have
gt; three columns of data, one contains account numbers, one dates and the
gt; third
gt; data to be summed. The difficulty is that there are duplicate entries and
gt; the account numbers go to 8 digits and I am summing to 6.
gt;
gt; customer-project-part Hours Date
gt; A B
gt; C
gt; 1234-02-06 20
gt; 01/01/06
gt; 1234-02-07 20
gt; 01/01/06
gt; 1234-02-08 20
gt; 02/02/06
gt;
gt; sum of 1234-02 =40
gt;
gt; The account 1234-02-07 is ignored because it is on the same date as
gt; 1234-02-06. The situation is that I am adding the total number of hours
gt; a
gt; machine is run but there are multiple parts going through at once. So I
gt; am
gt; adding only one occurance per project number for each day.
gt;
gt;
gt; Thanks
gt;
Thats working, thank you. Why is the date double negated?

quot;Peo Sjoblomquot; wrote:

gt; One way
gt;
gt; =SUMPRODUCT(--(C2:C10=--quot;2006-1-1quot;),--(LEFT(TRIM(A2:A10),7)=quot;1234-02quot;),B2:B10)
gt;
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt;
gt; quot;Toddquot; gt; wrote in message
gt; ...
gt; gt; how do I sum a column of data based on criteria in two other columns? I
gt; gt; have
gt; gt; three columns of data, one contains account numbers, one dates and the
gt; gt; third
gt; gt; data to be summed. The difficulty is that there are duplicate entries and
gt; gt; the account numbers go to 8 digits and I am summing to 6.
gt; gt;
gt; gt; customer-project-part Hours Date
gt; gt; A B
gt; gt; C
gt; gt; 1234-02-06 20
gt; gt; 01/01/06
gt; gt; 1234-02-07 20
gt; gt; 01/01/06
gt; gt; 1234-02-08 20
gt; gt; 02/02/06
gt; gt;
gt; gt; sum of 1234-02 =40
gt; gt;
gt; gt; The account 1234-02-07 is ignored because it is on the same date as
gt; gt; 1234-02-06. The situation is that I am adding the total number of hours
gt; gt; a
gt; gt; machine is run but there are multiple parts going through at once. So I
gt; gt; am
gt; gt; adding only one occurance per project number for each day.
gt; gt;
gt; gt;
gt; gt; Thanks
gt; gt;
gt;
gt;
gt;

It's because excel dates are numbers and to trick excel into converting a
date string into a number date it has to be put there as text and then
converted, you can use 0 or 1* instead but I have gotten used to this
You can replace that part with a cell like H2 and just put the date in H2
instead--

Regards,

Peo Sjoblom

nwexcelsolutions.comquot;Toddquot; gt; wrote in message
...
gt; Thats working, thank you. Why is the date double negated?
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; One way
gt;gt;
gt;gt; =SUMPRODUCT(--(C2:C10=--quot;2006-1-1quot;),--(LEFT(TRIM(A2:A10),7)=quot;1234-02quot;),B2:B10)
gt;gt;
gt;gt;
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; nwexcelsolutions.com
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Toddquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; how do I sum a column of data based on criteria in two other columns?
gt;gt; gt; I
gt;gt; gt; have
gt;gt; gt; three columns of data, one contains account numbers, one dates and the
gt;gt; gt; third
gt;gt; gt; data to be summed. The difficulty is that there are duplicate entries
gt;gt; gt; and
gt;gt; gt; the account numbers go to 8 digits and I am summing to 6.
gt;gt; gt;
gt;gt; gt; customer-project-part Hours Date
gt;gt; gt; A B
gt;gt; gt; C
gt;gt; gt; 1234-02-06 20
gt;gt; gt; 01/01/06
gt;gt; gt; 1234-02-07 20
gt;gt; gt; 01/01/06
gt;gt; gt; 1234-02-08 20
gt;gt; gt; 02/02/06
gt;gt; gt;
gt;gt; gt; sum of 1234-02 =40
gt;gt; gt;
gt;gt; gt; The account 1234-02-07 is ignored because it is on the same date as
gt;gt; gt; 1234-02-06. The situation is that I am adding the total number of
gt;gt; gt; hours
gt;gt; gt; a
gt;gt; gt; machine is run but there are multiple parts going through at once. So
gt;gt; gt; I
gt;gt; gt; am
gt;gt; gt; adding only one occurance per project number for each day.
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; Thanks
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;