1. Countif

I have 24k rows of data representing 3 months of guest visits and activity. There are 19,073 unique guests, most of which have only one visit, while other guests may have several visits or even several visits each month.

The business application we use creates a new record or "visit" when a new guest arrives or existing guest returns. The app tracks a guests' visit number which is one of the fields I want to work with in the exported records. I would like a formula that counts the unique visit number and places that count in the first row (record) for that guest in column F. For example, Guest 297218 (rows 32-36) has 5 visits. What would the formula be that counts the five visits (49, 50, 51, 52, 53) in col A for that guest out of the entire range of data?

I have attached a sample worksheet with sample data.

2. Part of the solution. If you put this ARRAY formula (ctrl+shift+Enter) in F6 and fill down, you'll count the visits in A.
But, only part of the solution because the results shows up for each guest number.

=SUM(IF(\$B\$6:\$B\$102=B6,\$A\$6:\$A\$102,0))

I haven't thought further about having it show up in only the first entry of the guest number. Maybe someone will.

3. Well, I think this works (still an array formula), but maybe someone will have a more elegant solution.

=IF(COUNTIF(\$B\$6:\$B\$102,B6)=1,A6,IF(COUNTIF(\$B\$6:B 6,B6)=1,SUM(IF(\$B\$6:\$B\$102=B6,\$A\$6:\$A\$102,0)),""))

Kevin

4. Hi Arcturus16a

Another approach to kweaver solutions, could be!

=IF(COUNTIF(\$B\$6:\$B6,\$B6)>1,"",SUMIF(\$B\$6:\$B\$102,\$ B6,\$A\$6:\$A\$102))
Non array.

5. Ah, good one, Kevin R.

Kevin W.

6. Both of these solutions SUM the number of visits rather than COUNT. In the example I used above Guest # 297218 has five unique visits (sequentially numbered 49, 50, 51, 52, 53). These formulas both generate the sum of the visit counts for a total of 255.

7. Hi

You want to count the number of times that each guest made a visit. If so, maybe!
In say I6 and copy down.
=IF(COUNTIF(\$B\$6:\$B6,\$B6)>1,"",COUNTIF(\$B\$6:\$B\$102 ,\$B6))

8. Yes. Thanks Kevin. That works. I think was hoping to learn of a solution that might use the guest # criteria to sum the unique visit #'s. But since each row (record) represents one visit and there can be only one guest # per record, then might as well just count the rows (of a guest's #).

Posting Permissions

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