Count column if NOT a duplicate (Excel 2010)

[lawitcher1]

I have this formula: =COUNTIF(M7:M635000,"*") written to count all instances a state is counted in column M. Can either a formula or code be written to only count the state counted, and if there is a duplicate count, not to count it twice.

[pecoflyer]

Have a look at http://www.get-digital-help.com/2009/06/09/count-unique-values-in-a-column-in-excel/

[lawitcher1]

I viewed and tried to study your recommended site, and am having trouble grasping the concept, and have not got it figured out yet. The adjustment that was made for the duplications in column "A" needs to be enhanced to not give an overall sum for the total numbers in cell "A5", but only the entry's that have been shown once. That means that if there is a duplication, it will only be counted once. These same parameters need to be added to Columns "M" & "N" and have the same sums to show in cells "M5" & "N5" as shown in Cell "A5", and duplicated names shown in cells "M3" & "N3" as shown in cell "A3". Please see the attached file. Sorry for being such a dummy and slow learner.

[NoSparks]

When I read the original question then followed Pecoflyer's link,
I thought this array entered formula in M5 would be a solution

=SUM(IF(M7:M635000<>"",1/COUNTIF(M7:M635000,M7:M635000),0))

that's committed with Ctrl+Shift+Enter together at the same time not just Enter
and Excel automatically puts in the curly brackets.

I suspect the same formula to be required in A5 and N5 (column adjusted accordingly).

Glad to see the range of concern has been substantially down sized.
Don't know how many contacts a ham operator makes but at 100 per day, 365 days a year, row 635000 is more than 17 years into the future.

and duplicated names shown in cells "M3" & "N3" as shown in cell "A3".

You'll need to explain the logic behind that.

To my line of thought there is 1 contact per call sign, multiple call signs per county, and multiple countys per state, so the only real duplicate is the call sign, the others would be "normal".

[lawitcher1]

Under normal circumstances, you are correct. In this instance there are contest rules (see CQP Party Beta.xmls that I posted) for a contest called California CQP Party. In this contest, you get a point for each contact (the call sign if not duplicated), each country worked (DX), a point for each state (duplicates will not give a point) and each county in California (again no duplicates allowed) that you make contact with. If a contact is made via CW (morse code) each contact is worth three (3) points, other wise you only get one (1) point. That is the reason I wanted to count the contacts in columns "M" (State) and "N" (County). I was going to address the "CW" vs "Phone" problem after I got this problem resolved. Please note that I have made two logbooks using your suggestions. 1) a general purpose logbook (as you rightly addressed) and 2) the CQP party logbook that we are addressing here. Another quirk is if a contact is made on a different frequency, the rules can be duplicated. This is not a concern in the general purpose logbook, nor is the counting duplicates, only in the CQP logbook.

Maybe I am entering it wrong. I just cut & pasted with ctrl+shift+enter and it will not sum or anything. It just shows a zero.

[NoSparks]

Don't know what you have in mind with regards to dealing with the duplicates entered in columns M & N.
This only identifies entries that are duplicates of anything already existing in the columns.

Altered the formula in A5, added formula for M5 and N5.

Altered validation for states to column G of the Reference Sheet.
Changed the formulas in columns R and U to match validation columns.

Hopefully something here will be of use to you.
Good luck with the project.

[lawitcher1]

Got everything working as wanted except that I screwed up the colors in Column L (previously M, as I deleted a column). I now have to address column "E" to have it count. It needs to count (if "Phone" a count of 2) and (if CW a count of 3). So, if cell E7 = Phone and E8 = CW, then the total number displayed in cell "E5" would be 5.

Strange how all of this starts coming together in my mind after looking at your suggestions. Just like sitting in a classroom.

Thank you sooooooooo much for your help. See attached for file updates.

[NoSparks]

I knew you would move the states and countys down to start at row 7
and I'd be willing to bet you also move those totals down around row 250 up to row 6.

At which time you will probably learn about rule #6 of the Excel/VBA Golden Rules.
Merged cells are great for cosmetic purposes but best avoided.
They are probably not too bad in your headers but can be a real hassle when using macros.

Try this in E5
=SUM(COUNTIF(E7:E3007,"PH")*2, COUNTIF(E7:E3007,"CW")*3)

[lawitcher1]

Thanks, that worked perfectly. You predicted perfectly what I did, and I DID learn from it. Here is a quirk that now needs to be addressed. I was informed (after the calculations were done) that cell A5 CAN be added as a duplicate under certain conditions, that being that the same person (call sign) can work any of the six bands (160m, 80m,40m,20m, 15m & 10m) on phone and get a credit (multiplier) for each band. The same also applies when using CW. If there is a duplicate for that call sign on these parameters, then it would NOT be counted. Here is a copy of the rules enhancements that were mailed to me tis morning:

MULTIPLIERS:
A. California Stations:
Count U.S. states (50) and Canadian areas (8): MR, QC, ON, MB, SK, AB, BC and NT where:
• MR = Maritime provinces plus Newfoundland and Labrador (NB, NL, NS, PE) • NT = Northern Territories (NT, NU, YT)
Maximum of 58 Multipliers

Note: Although the “CA” abbreviation is not used as a multiplier in this contest, the first valid CA county logged will count as the multiplier for California. California stations must log all CA contacts with their 4-letter county abbreviation; everything else as two letters, including DX for all stations outside of the U.S. and Canada.


B. Non-California Stations
Count all 58 California Counties for a maximum of 58 multipliers.

SCORE:
The final score is the total number of QSO Points multiplied by the total number of multipliers (58 maximum).
QSO Points = (CW Qs x 3 pts) + (Phone Qs x 2 pts)

Final Score = QSO Points x Multipliers

MODES: CW, Phone

BANDS: 160, 80, 40, 20, 15 and 10 meters.
Stations may be worked once on CW and once on Phone on each band.

Since I screwed up the color in the cells in columns K, L & M, would you also be kind enough to point me in the right direction to repair them. I was trying to get the duplicates to highlight.

[NoSparks]

Since I screwed up the color in the cells in columns K, L & M, would you also be kind enough to point me in the right direction to repair them. I was trying to get the duplicates to highlight.

You didn't screw anything up, you just didn't know to adjust the columns and ranges of concern in the worksheet_change procedure.
You've changed the colors I originally used and added to the time for UTC so I think the code is commented well enough for you to see where/what needs to be changed in there.
Give it a go, if you really screw up you can always get back to the starting point by downloading the last file you uploaded to the forum.

I'm going out to dinner shortly, I try to digest the points requirement a little later.