Sum without duplicates based on criteria from different cells

[perryadam]

I am able to sum the "#of accounts" worked by month on the Summary tab based on the data on the CSO tab. I also need the sum #of accounts worked without duplicates based on the ID#. I have tried multiple ways and can't get it to total correctly.

I would expect B7 on the summary page to be 8 (5+2+1) when the duplicate ID#'s are ignored for that month.

Any suggestions?

[rylo]

Hi

Select B7 on Summary, and create a new defined name with a name of rng, and a refers to: =OFFSET(CSO!$A$1,MATCH(Summary!B$3,CSO!$A:$A),0,SUM((MONTH(CSO!$A$5:$A$5000)=MONTH(Summary!B$3))*1),1)

B7: =IFERROR(SUM(OFFSET(rng,0,3)/COUNTIF(OFFSET(rng,0,2),OFFSET(rng,0,2))),"") This formula is array entered (ctrl, shift, enter). Copy across from B7 to M7.

HTH

rylo

[perryadam]

Thanks for the suggestion. At first it looked like this worked correctly for my needs but after testing it won't work. I think it is because it is referencing the match on the date not the ID#???

If I sort by the date the summary value changes or if a user enters a date out of order it will not display correctly.

I need a sum of Column D for only 1 instance of each ID# in Column C. Any other sugestions?

[benishiryo]

try this array formula:

=SUM((MONTH(CSO!$A$5:$A$16)=MONTH(B$3))*(MATCH(MONTH(CSO!$A$5:$A$16)&CSO!$C$5:$C$16,MONTH(CSO!$A$5:$A$16)&CSO!$C$5:$C$16,0)=ROW(CSO!$C$5:$C$16)-ROW(CSO!$C$5)+1)*(CSO!$D$5:$D$16))

[perryadam]

Thank you this worked perfect....