Sum based on criteria

[Guest]

I need to sum the number of occurances of a problem based on a product code in the col. to the left. This also needs to only sum if it happened between two dates which are going to be entry fields. I have tried everything I can think of with no luck. Does anyone know what formula to use for this?

Sheet 1
A B C D
Start date 7-1-03 End Date 10-20-03

Prod code # of Occur.
1 (help me here) should be 2
2 (help me here) should be 11
3 (help me here) should be 27


Sheet 2

Date of Occur. Prod. code #of Occur.
6-1-03 1 10
6-15-03 2 5
7-1-03 3 6
7-1-03 3 6
9-1-03 1 2
9-14-03 3 15
10-1-03 2 5
10-19-03 2 6

 

[Bob Phillips]

Alex,

I don't know where the product codes are so I have embedded them in the
formula rather than reference the cell, so just change for that

=SUMPRODUCT((Sheet2!$B$2:$B$9=1)*(Sheet2!$A$2:$A$9>=B1)*(Sheet2!$A$2:$A$9<=D
1)*(Sheet2!$C$2:$C$9))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I need to sum the number of occurances of a problem based on a product

code in the col. to the left. This also needs to only sum if it happened
between two dates which are going to be entry fields. I have tried
everything I can think of with no luck. Does anyone know what formula to use
for this?

Sheet 1
A B C D
Start date 7-1-03 End Date 10-20-03

Prod code # of Occur.
1 (help me here) should be 2
2 (help me here) should be 11
3 (help me here) should be 27


Sheet 2

Date of Occur. Prod. code #of Occur.
6-1-03 1 10
6-15-03 2 5
7-1-03 3 6
7-1-03 3 6
9-1-03 1 2
9-14-03 3 15
10-1-03 2 5
10-19-03 2

6

 

[Martin]

Hi Alex,
My suggestion would be to name two variables
Start_Date and End_Date
Enter the dates in these names
Add a Lookup column with a nested if statement for each row
if(mycol_date > Start_Date,if(mycol_date <
End_Date,occur_col_value,0),0)
This will put the occurrances you want in the lookup
column where you can use the SUMIF worksheet function to
sum based on your product code.

Martin

-----Original Message-----
I need to sum the number of occurances of a problem based

on a product code in the col. to the left. This also needs
to only sum if it happened between two dates which are
going to be entry fields. I have tried everything I can
think of with no luck. Does anyone know what formula to
use for this?

Sheet 1
A B C D
Start date 7-1-03 End Date 10-20- 03

Prod code # of Occur.
1 (help me here) should be 2
2 (help me here) should be 11
3 (help me here) should be 27


Sheet 2

Date of Occur. Prod.

code #of Occur.

6-1-03

1 10

6-15-03

2 5

7-1-03

3 6

7-1-03

3 6

9-1-03

1 2

9-14-03

3 15

10-1-03

2 5

10-19-03

2 6