sum based on multiple criteria

[Guest]

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

 

[Peo Sjoblom]

One way

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



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Guest]

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

 

[Peo Sjoblom]

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