Thanks:  0
Likes:  0

# Thread: sorting with a countif needed

1. 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!

2. 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

3. Do you have time in a cell as number like

2

or

2 hrs

4. just 2

Thnks,
Dawn

5. 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

6. 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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•