CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
BBekyarov
BBekyarov

Reputation: 187

Adding two criterias in excel formula

I am having a little issue here. I was trying to add a criteria for a range of column cells but it doesn't seem to work properly. My case is this : I must add only two digit numbers and also without having them duplicate anywhere in that range. Let the range for example be like A4:A8. I tried this but it doesn't get accepted by the Excel

=AND(LEN($A$4)=2,COUNTIF($A$4:$A$8,A4:A8)<2)

Can you please advise me on where I am wrong because it's really frustrating to handle.Thanks in advance!

Upvotes: 4

Views: 153

Answers (1)

Sam
Sam

Reputation: 7303

This will add all unique 2 digit numbers in range A4:A8

=SUM(IF(LEN(A4:A8)=2,IF(COUNTIF(A4:A8,A4:A8)=1,A4:A8),0))

you'll need to enter it as an array formula with ctrl + shift + enter

Upvotes: 4

Related Questions