Countif formula?? 1

[VictoriaLJones]

I'm not sure if this is the correct forum, but thought would give it a try.

I am trying to write some formula's into a spreadsheet (that will eventually be automated). What I am trying to do is pick up where there are multiple entries of the same value, but the number of times it appears is an odd number.

For example the following data shows where the value 140 occurs multiple times but it is an even number - i.e. 10 times. However the next value 120 only occours 3 times. In this scenario I would only want it to flag "Multiple" for the 120 value, not 140.

At present my formula flags both. The formula I am using is:
=IF(L1="",IF(L2="","One Sided","&quot,IF(L1="Match",IF(L2="Match",IF(L3="","Multiple","&quot,"&quot,"&quot)

The "Match" field is simply an If statement comparing the current row value to the row aboves value.

How do I get it to ignore cases where the number of entries are "even". I am thinking of using a CountIF function - but what should I use as a criteria?? Its starting to make my brain hurt, so any suggestions gratefully received!!

Thanks
Victoria

Multiple?? Value Matching?

140 Match
140 Match
140 Match
140 Match
140 Match
140 Match
140 Match
140 Match
Multiple 140 Match
140
120 Match
Multiple 120 Match
120

 

[TonyJollans]

Hi VictoriaLJones,

Assuming your data is in L2:L100, then in K2 put ..

=IF(L2=L1,"",IF(MOD(COUNTIF(L$2:L$100,L2),2)=1,"Multiple","&quot)

.. and copy it down to K100. This counts the number of occurrences and checks its remainder when divided by 2.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[VictoriaLJones]

Tony,

Many thanks for that - it works except for one thing. It now highlights them all as Multiple and I am loosing the option to pick out if the entry is "One Sided", i.e. it is a single entry, as per my previous IF statements. I am trying to incorporate the two, but having a few problems.

Would you mind telling me where to put the "One Sided" argument in the formula you have written.

Many thanks for all your help!
Victoria

 

[TonyJollans]

Hi Victoria,

I'm sorry, I didn't read your formula fully and now that I have I don't really follow it. Your example doesn't show 'one-sided' so this is a bit of a guess.

I haven't used your match column - just checked the list of numbers. If you have a special case when there is only one entry, then try this amendment ..

=IF(L2=L1,"",IF(MOD(COUNTIF(L$2:L$100,L2),2)=1,if(COUNTIF(L$2:L$100,L2)=1,"One Sided","Multiple&quot,"&quot)

If that isn't right could you give a bit more sample data.. thanks.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[VictoriaLJones]

Tony,

OK, here is some additional examples - sorry!! Below you will see an entry where the value is "330". There are no matched vlaues or multiple entries of that value. In my version of the formula I flag the value as 1 sided if the cell above does not match:

=IF(L1="",IF(L2="","One Sided","&quot,IF(L1="Match",IF(L2="Match",IF(L3="","Multiple","&quot,"&quot,"&quot)

Data:
280 Match
280 Match
280 Match
280 Match
280 Match
280 Match
280 Match
280 Match
280 Match
280 Match
280 Match
280 Match
Multiple 280 Match
280
320 Match
320
One Sided 330
350 Match
350

Thanks for your help!!

Victoria

 

[VictoriaLJones]

Ooops!!

I should also mention that column L refers to the column containing the "Match" data/values

Thanks
Victoria

 

[TonyJollans]

Hi Victoria,

if yu change the reference to column L in my formula to the column with the numbers, it should do what you want. I have just been called away, I will check back tonight to see how you got on.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[TonyJollans]

Hi Victoria,

How did you get on? I've just had another look and a slight change will make the "Multiple" and "One Sided" indicators appear alongside the last, rather than the first, occurrence, as per your list. If your numbers are in column K (rows 2 to 100 in this example), then (without any need for the "Match" flagging in column L) this should do it:

=IF(K2=K3,"",IF(MOD(COUNTIF(K$2:K$100,K2),2)=1,IF(COUNTIF(K$2:K$100,K2)=1,"One Sided","Multiple&quot,"&quot)

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[VictoriaLJones]

Tony,

Sorry I didnt get back to you yesterday, all got a bit crazy here in the afternoon - XP migration!!!

Your final post has done the trick - THANK YOU!!! )

Star from me!!!

Victoria