sorting with a countif needed

[dawnfoss]

I'm trying to sort a ten column report


Col 1 Col 2 Col 3
elaps. elaps
Place Time Place Time Place Time
Row 1 ab 1 hr xy 2 hrs ab 1
row 2 xy 1 hr ab 1 hr xy 1

There can be ten different kinds of places, ie: ab, xy, cd, fa, etc.

I need to be able to have a formula that will add two or more times together If the location is the same in the same row. Then I need to count that result only if it is greater than one hour. Ie in row one if ab appears twice then i need to add their times. As per above that would be "ab" appears twice and it is greater than an hour. xy appears once and it is > an hour . Total count for ab for that row is 1. Total count for xy =1.

But I have ten different locations that i will have to analyze.

Help!

 

[dawnfoss]

wow, my data came over all jumbled up as per how the table looks. Sorry. Hopefully the question will make sense. How do i post a small sample of a sheet?

dawn

 

[Aladin Akyurek]

Do you have time in a cell as number like

2

or

2 hrs

 

[dawnfoss]

just 2

Thnks,
Dawn

 

[Yogi Anand]

On 2002-04-25 09:37, dawnfoss wrote:
I'm trying to sort a ten column report


Col 1 Col 2 Col 3
elaps. elaps
Place Time Place Time Place Time
Row 1 ab 1 hr xy 2 hrs ab 1
row 2 xy 1 hr ab 1 hr xy 1

There can be ten different kinds of places, ie: ab, xy, cd, fa, etc.

I need to be able to have a formula that will add two or more times together If the location is the same in the same row. Then I need to count that result only if it is greater than one hour. Ie in row one if ab appears twice then i need to add their times. As per above that would be "ab" appears twice and it is greater than an hour. xy appears once and it is > an hour . Total count for ab for that row is 1. Total count for xy =1.

But I have ten different locations that i will have to analyze.

Help!

Hi Dawn:
If I have understood you correctly, and
your 2 rows of data is in cells A1:F3, then use the following formula in cell G2 and drag down

=B2+IF(A2<>C2,0,IF(B2>1,B2,0))+IF(A2<>E2,0,IF(B2>1,B2,0))

with your data it will result in 1 in cell G2, and 1 in cell G3

HTH

 

[Aladin Akyurek]

On 2002-04-25 10:46, dawnfoss wrote:
just 2

Thnks,
Dawn

I'll assume that A1:F3 houses the sample data you provided:

{"Place","Time","Place","Time","Place","Time;
"ab",1,"xy",2,"ab",1;
"xy",1,"ab",1,"xy",1}

Enter unique place names in row 1 from G1 on. For the sample we have:

ab in G1, and
xy in H1.

In G2 enter:

=(SUMPRODUCT(($A2:$E2=G$1)*(ISNUMBER($B2:$F2)),$B2:$F2)>1)+0

Copy this first to H2 then down.

Aladin