Count the different data 3

[GoDawgs]

Hopefully I'm just missing a function I don't know about yet...but here's my problem:

I've got a data sheet with Date, Department, Category, and Quantity. Basically if this were an actual database table the primary key would be Date, Department, and Category combined.

I need to count how many different dates I have in the table. Say the date data column looks like this:

1/1/04
1/1/04
1/1/04
1/1/04
1/2/04
1/2/04
1/3/04

So there are 7 records there, but only 3 different dates...I need to somehow return 3. Any help would be greatly appreciated.

Kevin

 

[bluedragon2]

SUBTOTAL

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

You can use the SUBTOTAL function, or just highlite your data and do Data/Subtotals choose the date column and pick count.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[KenWright]

Assuming your dates are in say A1:A100

=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))

but with data like this you should really be considering the benefits that using pivot tables will bring you wrt analysis of that data.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

b'dum tish - Ken - how many times do you reckon Pivottables are cited as being the answer to problems in this forum. It's gotta stand at about 5-10% of all questions ;-)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[GoDawgs]

Thanks for everyone's help. I ended up using Ken's response because I'm just looking to fill a cell with a count of how many day's worth of data I have in the table...I am a bit confused by the formula though, can anyone give me a quick explanation of why it works?

Thanks for the pivot table tip too...I'm an Access guy mainly so when I was told to set this report up in Excel I did plenty of searching on here...and did end up trying out the pivot table, but in the end ended up using sumproduct formulas on the data (dumped directly from an Access query). It works great, and looks the way "they" want, so I'll stick with it now. Thanks for the tip though.

Kevin

 

[GlennUK]

Hi GoDawgs,

I give a full explanation of this type of formula in this thread ...

thread68-552708

CHeers, Glenn.

 

[GoDawgs]

Ah, got it...thanks for your help Glenn.

 

[KenWright]

Hi Geoff, I reckon that if you wrap SUMPRODUCT and Pivot Tables up together, you could probably cater for one hell of a lot of all the messages posted

Just luuuuuuuuuuvvvv those Pivot Tables

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

Maybe we should try to write a combined FAQ for summarising data - starting from optimal layouts and ending with standard practices / formulae.....could be a bit of a large FAQ and it would mean that probably 30% of all posts here could be answered with "Please see FAQ xx-yyyy" ;-)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question