autosum if column has same values, but more than single cells,

[parkkibum]

Hi guys,

i have attached a file with more details.

I like to know if it's possible to highilght or put borderline, based on values in one columb
for example.

abcd
abcd
abab
abab
abcc
abcc

above column has 3 sets of same values, i like to distinguish them by same value.

is there any way to highlight or distinguish if they have same value, i tried duplicate value in conditional format but it highlights all values, i need more of grouping if they have same values and highlight in different color if possible. and i'm not familiar with VBA.
so plain formula would be best.

[martindwilson]

as long as they are grouped together as shown
in g2 filled down
=IF(COUNTIF($C$2:C2,C2)=COUNTIF(C:C,C2),SUMIF(C:C,C2,F:F),"")

[Leif Magnus]

Are the values in column C constantly changing? or will you be dealing with different values in the c column all the time?

[parkkibum]

TO Leif Magnus,

Yes I do this monthly but there will always be many sets of duplicated number.and I need to do sumifs of invoice amount if they have duplicate reference number..

[Leif Magnus]

Well to keep it simple you could just: =SUMIF(C2:C18,"laae005015",F2:F18)
If you dont have a huge amount of different categories in column C that would work you could just change the criteria for each one.

[martindwilson]

that is strange, just sort by col c and use what i posted ,have you even read that?

[Leif Magnus]

Ya what Martin said. I couldnt get it to work at first but ya that is a nice function.
Could you explain that in words as well Martin?

[parkkibum]

Martin,

Thanks a lot!

How in the world did you do that?!??!

i'm jealous now, can you maybe tell me how that function works?? thanks a lot!!

[martindwilson]

=IF(COUNTIF($C$2:C2,C2)=COUNTIF(C:C,C2),SUMIF(C:C,C2,F:F),"")
first bit
COUNTIF($C$2:C2,C2) when dragged down will change to
=COUNTIF($C$2:C2,C2)which =1
=COUNTIF($C$2:C3,C3)which =2
=COUNTIF($C$2:C4,C4)which =3
=COUNTIF($C$2:C5,C5)which =4
giving 1,2,3,4,5..... for each value that matches
down to =COUNTIF($C$2:C10,C10)which =9
it then starts again and as there is a new value in c11
COUNTIF($C$2:C11,C11) =1
second bit
compare that result against the total count for each item
=COUNTIF(C:C,C2) since c2 or c3 or....... is repeated 9 time the answer is 9
so when COUNTIF($C$2:C2,C2)=9 then sumif the all the results that = c9
if at any point count so far <> the total count of the respective cell put ""(""=blank)

[parkkibum]

Thank you so much martin!

[Cutter]

@ parkkibum

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.