sum based on multiple criteria
 

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.


Thanks

sum based on multiple criteria
 

One way

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



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Todd" wrote in message
...
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.


Thanks

sum based on multiple criteria
 

Thats working, thank you. Why is the date double negated?

"Peo Sjoblom" wrote:

One way

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



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Todd" wrote in message
...
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.


Thanks

sum based on multiple criteria
 

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

http://nwexcelsolutions.com


"Todd" wrote in message
...
Thats working, thank you. Why is the date double negated?

"Peo Sjoblom" wrote:

One way

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



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Todd" wrote in message
...
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.


Thanks