PDA

View Full Version : [SOLVED] Return data not duplicated via formula?



elsg
05-02-2014, 05:41 AM
My friends.
I want a formula to....
I need to count (without duplication) the data in my database, based on 3 criteria (Date, PRP, AGR).

how to return data not duplicated via formula?

Kenneth Hobs
05-02-2014, 05:59 AM
You can use =SumIfs or =SumProduct. For example in O4:
=SUMPRODUCT(($J$4:$J$37=J4)*($L$4:$L$37=L4))

elsg
05-02-2014, 06:10 AM
i need make it considering range(A3:C454).
the range used by you, I left the tab only to explain how to get where I want.


how to get this result used (original My Data Base)?

Bob Phillips
05-02-2014, 06:13 AM
Try this array formula

=COUNT(1/IF(($A$4:$A$454=$E4)*($C$4:$C$454=F$3),MATCH($B$4:$B$454,$B$4:$B$454,0)=ROW ($A$4:$A$454)-ROW($A$4)+1))

elsg
05-02-2014, 06:26 AM
I make something wrong, because result it ZERO in all cells.

elsg
05-02-2014, 06:28 AM
Sorry, i find my error.

it's work great!!

Thank you very much!!!!!!!!!

Kenneth Hobs
05-02-2014, 07:25 AM
I usually have fits with array formulas.

Obviously, my method can be adapted to your range. I tried to show you that it validated what you had.

For the actual data, in H2, copy and fill to I2.
=SUMPRODUCT(($A$4:$A$454=$E4)*($C$4:$C$454=F$3))

Bob Phillips
05-02-2014, 08:46 AM
I don't think so Kenneth, he seems to want the number of unque Valores within the two given conditions.