Hi all, how you all doing ?

[Atomic Mitten]

Hi guys,

just thought I would say hi to all that know me, hope your all well ?

[Andrew]

All is well except a research paper. I dislike research papers.

Was going to make a thread about this question, but figured I might as well ask here since most people will be viewing this thread. :)

I can not figure out how to make a certain formula in Excel.

I want the formula to count each date only once (dates take up 4 horizontal cells (Friday May 27 2005)), and to count the number of different dates in a given area (say between A13:A(D?)104).

Say I have 5 clients, each with different and multiple dates assigned to each (Grass was cut on this date and this date, etc). I want to count the number of different dates these 5 clients have as a whole. So if all 5 clients have a similiar date under them, it will only count this date as 1 (once).

Thinking of a =countif() equation, but none have worked.

Later I have to go try and map a field using GPS. Tried it earlier but it didn't send correctly to my computer from the iPAQ which ended up screwing all the data I had collected today. I havn't been socializing much. Too busy. Should phone up some friends.

How you been doing?

[Kiyouta]

I've used Excel a lot in the past, and I really had nothing better to do tonight so I'd thought I'd help ya. :P

Is this essentially what you wanted it to do? Sorry if my annotations made it seem more complicated than it is! :-

[attachment archived by Gobalopper]

[Dante]

How am I doing? Well I've crapped blood, coughed up something yellow, felt my gums peel and bled from the nose. Not dead yet though!

(Also, I think I'm recovering)

[Andrew]

Works like a charm.

Only "problem" is that I have the formula done all the way down a column in the spreadsheet, so there are hundreds of #VALUE! (which is normal as there are no dates to get from the cells). And this leads to a #N/A in the output of the =SUM(IF(FREQUENCY formula. I don't really want to put in the =datevalue formula each time I enter a new date (even if it only involves copy/paste :P). Much easier if the =datevalue formula is there already when I punch in a date, as it would automatically calculate dozens of other formulas each time I enter a date.

For an example of what I mean, simply erase one of the dates (the "May 27 2005" kind) in the example you gave me. This leads to the whole #VALUE! and #N/A thing.

Thanks!

Where you been hiding Atomic?

[Super-ness]

I'm tying my shoelaces :D

...Silly me, I'm not wearing any shoes  :'(

[Kiyouta]

Yeah I know what you mean, but really dates shouldn't be split up in 2 or more cells, and in order to compare the dates you have to consolidate them into a single-celled date. You could always make a new column at the end of the 3-celled dates and fill it with the =DATEVALUE formulae but I don't know how practical that would be for you, as I don't have the original workbook.

OMG KaL, you're so right as always. I do need new shoelaces. :O

[Andrew]

I do have a column for the =datevalue at the far right side out of the way as it is not important to see. I would have the =datevalue formula in that column all the way down the spreadsheet.

I have the date in 3(4) separate columns so I can do a

=COUNTIF(Customers!$A$13:$A$587,"Monday")

=COUNTIF(Customers!$B$13:$B$587,"May")

etc, in a different worksheet so I know what day/month I am the busiest (and to get as many stats as possible).

Unless your saying I can do the =countif if the date (including day of the week ie. Tuesday)is in a single cell.

But it doesn't really matter. I can just add the formula every so often to get the stats from the different dates. I've got so much stats from just the dates and prices, and expenses its rediculous. Don't know how I'm going to incorporate the weather data. Damn finance class, teaching me something.

The shoes I currently wear (the good ones I wear to the bars etc, not work shoes) are about 5 years old. One of the shoelaces is kinda broken and long. Its annoying. Going to need new shoes this fall. And a new hat. Probably will buy a 24 of beer and get a free hat.

[lowzeewee]

Hi Atomic! You are still in the virtual Fed2k Football first XI! You play central defence and have started all but one of our 13 games this season. A cracker of a home game against Sunderland begins later. We are unbeaten at the top of the table with 33 points and Sunderland is following closely behind with 31 points.

[Cybopache]

Hello you old oak tree !!

how are you doing my friend, me and my familia are very well thx.

I still got the same e-mail adress..

untill later...

Cybo

[Tako]

could be better, just got a paycut on my salary, seems they payd me to much the last time >:(

thats gonna cost me... :'(

[erjin999]

Hi atomic ! Long time no see. you back now or just popping in ?

Get that rusty old nick back in action, atomic254  :P

[lowzeewee]

Atomic I believe you are still blocking me on MSN? ;D

[Apollyon]

Where the 'ell you bin?  :P

[Kiyouta]

Hehe, he's gone again. :D

[lowzeewee]

He was busy defending against Sunderland's futiles efforts at scoring...We clinched this crucial fixture against them 4-0! Yes you read that right! 4-0! We draw against crap teams but can thrash big teams... :O 'Big-Time Charlies', we are.

[Atomic Mitten]

I've not been very well for sometime, but I am recovering now and hope to be back fairly soon on a regular basis.

[Andrew]

Yeah I know what you mean, but really dates shouldn't be split up in 2 or more cells, and in order to compare the dates you have to consolidate them into a single-celled date. You could always make a new column at the end of the 3-celled dates and fill it with the =DATEVALUE formulae but I don't know how practical that would be for you, as I don't have the original workbook.

I just tried using the =countif(a8:a20,February) for example to see if it could count the number of times february occurs within a cell range that is using the date category format. It does not countif anything in those types of cells. So I will have to use the 4 separate cells for dates in order to countif them. Because using that tells me that sales were down 8.9% in May over the same period last year (But June is looking good so far).

But I'm still using the =datevalue formula on the far right side of a worksheet. It's working wonders with =sum(if(frequency..... :)

in order to compare the dates you have to consolidate them into a single-celled date.

Unless I'm missing something? It doesn't seem to do =countif

Unless you were just talking about the =sum(if(frequency formula.

Hmm, seems to partially work with =count(if(  formula, although not very good, and it makes the formula more complicated.

Hope you get better Atomic. :)