Count if with sum, sign, frequency.

**mjr10891** · 03-02-2013, 08:30 AM

Hi to all

i have a problem like this.

Open 3000354120
open 3000354123
closed 3000354121
open 3000354123
closed 3000354122
open 3000354123
closed 3000354124
open 3000354121
closed 3000354126
open 3000354127

and i want countif only open and numbers and not count duplicates " with using sum(sign(frequency(b1:b11,b1:b11))) "

i know how to not count duplicates with using =sum(sign(frequency(b1:b11,b1:b11)))

i hope u all understand...

I am attaching excel file for your reference...

Thanks in advance...

Mjr

**benishiryo** · 03-02-2013, 08:46 AM

hi Mjr, not sure if i got you right because i dont see a pattern with those colours. to count those numbers without duplications & with "Open" in column A, try:
=SUM(SIGN(FREQUENCY(IF(A1:A10="Open",C1:C10),C1:C10)))

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

**mjr10891** · 03-04-2013, 02:08 AM

Thank u very much benishiryo.

i am asking one more fever from u.
A C D
Open 3000354120 yes
open 3000354123 no
closed 3000354121 no
open 3000354123 no
closed 3000354122 yes
open 3000354123 no
closed 3000354124 yes
open 3000354121 no
closed 3000354126 no
open 3000354127 yes

with above mentioned data.

countif only open and no and numbers and not count duplicates is that possible

I hope u understand what i am saying.

I am attaching file for your reference.

thanks in advance....

MJR

**benishiryo** · 03-04-2013, 02:16 AM

you're welcome. then try:
=SUM(SIGN(FREQUENCY(IF(A2:A11="Open",IF(D2:D11="no",C2:C11)),C2:C11)))

if you're satisfied with the answer, please mark it as "Solved". my signature in the thread will guide you how to do that. if you are drifting further away from the opening post, do start a new thread. thanks