Counting only unique values

[boll55]

Hi,

On my sheet I have a series of countifs functions. The last one I need (L2) is to count three Criterias (Within a date range (Violations A:A), the level it falls in (Violations K:K), and inspections number (Violations C:C)) The inspection numbers however duplicate and I only want to count them once.

Help please!

Nothing drives me more crazy than when i get to the last part of a function and i cant figure it out!
Book1 revised.xlsx
Thanks so very much

[icestationzbra]

enter this array / CSE formula in 'Sheet1 (2)' cell L2.

Please Login or Register  to view this content.

then, you can drag it right.

i got the result of 3 for dates in the range 23-06-10 and 28-07-10, for Level 1. by the way, what is X1? i could not find any corresponding column in your database, so i just left it alone.

in case you just want to find the unique count for a specific date, you would simplify the formula thus:

Please Login or Register  to view this content.

EDIT:

the above formula had an error. i have fixed it; see the attached file. in order for the formula to be as automated as possible, the table header on Sheet2 (2) had to be changed to the one highlighted in Yellow and Red, with right-most values being X1, X2 and X3. sans that, the formula would become very cumbersome.

also, keep in mind that you have to explain your needs well. as you can see, in this case, it took a couple of rounds for you to explain your requirements well. this sort of back and forth is a waste of time for both parties.

[boll55]

This doesnt seem to be working for me

[boll55]

I got it to work! Thanks so very much !

[boll55]

Ok one more problem....I have it working in the first row of boxes but now i need to move it down to the next row and change the dates and i keep getting 1 which is not the right answer