Using Sumif where criteria appears more than once

[kev8279]

Hi Guys,

I'm trying to use the sumif function to bring back data on to a list where the users appear more than once. What I want to do is only show the results once even if the user appears multiple times. I cannot use a pivot as I need to pivot all of the data once I get this sorted. Can anyone help

 

[barry houdini]

Hello kev, welcome to MrExcel.

so if you have users in A2:A10 and you are using a SUMIF formula in B2:B10 try amending that to something like this

=IF(COUNTIF(A$2:A2,A2)>1,"Repeat","original_SUMIF)

replace original_SUMIF with your formula

 

[kev8279]

Thanks for the reply Barry, that works a treat!! Just out of interest how does this work as I would have thought the countif would also give the same result.

Just being nosy, thanks so much for the help

 

[barry houdini]

As you copy the formula down the range for the COUNTIF changes, it always looks at the cells on that row and above only...so for the first occurence of any name COUNTIF will give you a 1 and the SUMIF is used.....but for all subsequent appearances of that name COUNTIF will count 2 or greater and give you "Repeat". Of course you can change the text to whatever you want, or return a blank by using ""

 

[kev8279]

Of course, genius!!

That will save me a lot of frustration tomorrow at work.

Thanks again.