Counting votes but filtering out >1 vote by same person

Doug

Try replacing C3:C in the formulas with INDEX(UNIQUE(B2:C),,2)

=COUNTIF(INDEX(UNIQUE($B$3:$C),,2),I3)

=SUMIF(INDEX(UNIQUE($B$3:$C),,2),I3,$G$3:$G)

Hi Norie,

Thank you for your effort. It is appreciated even if it doesn't work as expected.

I experimented with your suggestion, I think you did too, in columns N-O? But the suggested formula returns incorrect values.

I am trying to understand how the formula works because, for instance, "Silent Blazers", changes from 272 (correct value) to 300 (incorrect value), another example is "Merry Marvels" changes from the correct 84 to 72, etc.

Whereas the desired values for the false repeaters, are listed in column M, and column N explains what should be counted.

Doug

Are the correct no of votes being returned?

Do you want to exclude the duplicate votes from the adjusted total value?

Hi Norie,
> Are the correct no of votes being returned?
The data in columns A - C would be on-going (pulled with IMPORTRANGE), this is just a sampling of typical data.

>Do you want to exclude the duplicate votes from the adjusted total value?

Almost. I want to exclude all but one of the duplicate votes (Voter (B) + Team voted for (C)). Ideally, I want to keep their last vote made.

In the subscription version of desktop Excel or Excel Online, you now have the dynamic arrays feature. This may be used to tally your votes. Copy down the following formula to return your desired results:

=SUM(IFERROR(XLOOKUP(UNIQUE(B$3:B$1001),FILTER(B$3:B$1001,C$3:C$1001=I3),FILTER(G$3:G$1001,C$3:C$1001=I3),0,,-1),0))

Select allOpen in new window

I don't use Google Sheets at all. When I posted the above formula in your Google Sheets workbook, I got 0 for the result. It may be that the formula needs some ArrayFunctions sprinkled in.
Count last vote only.xlsx

Another approach is to use an auxiliary column. I believe this approach would work even in Google Sheets.

The auxiliary column formula determines whether a vote should be counted. If so, it returns 1; otherwise 0. I put this formula in cell H3 and copied it down. Note its use of a test range offset by one row.

=--(COUNTIFS(B4:B$1001,B3,C4:C$1001,C3)=0)

Select allOpen in new window

You may then tally votes using this auxiliary column in a simple SUMIFS:

=SUMIFS(G$3:G$1001,C$3:C$1001,I3,H$3:H$1001,1)

Select allOpen in new window

Doug

How would you determine which vote(s) to eliminate, or which votes to return?

It might be possible to extend the UNIQUE function to cover the other columns but that wouldn't work as desired with the current data as the values in columns D to F aren't always the same for each team instance.

For example, the values in D to F for Mean Beluga Whales aren't the same in each row - see below.

Team voted for             Meets standards Company values Overall Acceptance
Mean Beluga Whales             4                          4                          6
Mean Beluga Whales             4                          5                          6
Mean Beluga Whales             2                          4                          4

If you were to use UNIQUE on a rang extended to include these other columns then these 3 rows would still be returned.

P.S. Regarding votes being correct, I was referring to the current data.

Hi Norie,

> How would you determine which vote(s) to eliminate, or which votes to return?

I was thinking that only the last vote would be counted. For instance,


Jesse.jenkins voted twice so, I would like to only accept the last vote. So the calculation should be 100 + 72 + 76 = 248.
If Jesse.jenkins voted again for Brute Infernos, then 76 would be replaced with the next value.

>P.S. Regarding votes being correct, I was referring to the current data.

The values in column Total Value (K) are the actual unadjusted numbers.
The values in column Adjusted Total Value (L) are not correct. At least not now.
The manually written values in column Should Be (M) are the desired values that I am looking for. Column N shows the (G) values being added.

Thanks again for your effort. :) 

I solved my problem, it works but it is horribly messy. Hopefully, my solution can be improved by someone with better skills than me. :)

Basically I solved the problem by breaking down the requirements into simpler tasks that I could easily apply a formula to solve.

See tab test sheet copy for my monstrosity. Try not to laugh. LOL

I explained each step in row 2, but here it is:

Combine time email address, team vote, vote score (Q, R)
=CONCATENATE(A3,",",B3,";",C3) 
Combine the time to sort in reverse chronological order (see next step). Because for duplicate votes (same voter on same team), I want to accept only the last vote. Note that I am using two delimiting characters so that do two separate splits in subsequent steps.

Sort on date (T, U)
=SORT (Q3:R,1, True, 2,false)
Sort in reverse chronological order.

Split out date (W, X, Y)
The data is sorted, I no longer require the date information.
=split(T3, ",")

Find unique in X (AA, AB)
=sortn(X3:Y,9^9,2,1,TRUE) 
This function will give me the final data that I need for adding the final results. All duplicates (same name on team) are removed, only the last vote of each duplicate remains.

Final Split (email and team) (AD, AE, AF)
Split out email and team name so that teams can be matched against Team List in column (AH)      

Columns AH - AJ are the same as in the original sheet. Column AK it the manually calculated results, used to validate that column AJ is correct.

Now, my question is, can all those steps be reduced with more clever formulas?

Thanks for your help. :) 

Thank you so much byundt. Simply brilliant! :)

And thank you to Norie, as well. :)