COUNTIF query

[golszews]

Hello there,

I would describe my level of proficiency at excel to be above intermediate.
However, despite all attempts, I cannot come up with a simple function:

I have a spreadsheet of various data, including names, simple yes/no
entries and DATES. It is with the dates (regardless of how they formatted
to appear) that I am having difficulty with. I need to sum the number of
times a date (eg. any day in April 2002) appears in a particular column.

name1 surname1 21/04/2002
name2 surname2 02/04/2002
name3 surname3 03/03/2001
name4 surname4 17/04/2002
"" this is where there needs to appear "3"

I have tried every variation of COUNTIF, COUNT, SUM, etc, etc.

Please help. Thankyou.
Gosia

Gosia.Olszewski@uts.edu.au

[PaddyD]

Gosia,

Don't know what going wrong with the countif problem (which I managed to replicate), but you could use the following entered as an array formula (using Control + shift+ enter):

=SUM(IF(MONTH(B20:B23)=4,1,0))

Hope this helps,

Paddy

[Yogi Anand]

Here is another array formula ... (if your data is in cells B20:B23, as Paddy assumed):

{=SUM((MONTH(C1:C4)=4)+0)}

Regards!

[PaddyD]

Yogi (or anyone else)...

any idea why the original countif version didn't work (for me, it fell over when trying to xtract the month portion of the dates)? I can only guess that countif doesn't like evaluating some sorts of expressions?!?

Paddy

[Yogi Anand]

On 2002-05-19 19:42, PaddyD wrote:
Yogi (or anyone else)...

any idea why the original countif version didn't work (for me, it fell over when trying to xtract the month portion of the dates)? I can only guess that countif doesn't like evaluating some sorts of expressions?!?

Paddy

Hi Paddy:
I don't believe that the criterion portion in CountIf allows for a computation (such as month(c1:c4)=4)

However if I compute the months and add them in corresponding cells in column D, I could easily use;

=COUNTIF(D1:D4,"=4")

Regards!

[ This Message was edited by: Yogi Anand on 2002-05-19 20:03 ]

[PaddyD]

Yogi,

Thanks!
Paddy

[Yogi Anand]

Hi Paddy, I just edited my earlier post in regard to use of COUNTIF to indicate that if I did use a column D to show computed months, I could use

=COUNTIF(D1:D4,"=4")

[PaddyD]

Yogi,

Thanks again. I generally use sum as an array for 'countif' purposes - it provides you with all the flexibility of the rest of excells functions.
It might also be worth pointing out the 'countif' has known (although obscure)bugs (see micrsoft site for details)
Paddy

[Aladin Akyurek]

On 2002-05-19 18:05, golszews wrote:
Hello there,

I would describe my level of proficiency at excel to be above intermediate.
However, despite all attempts, I cannot come up with a simple function:

I have a spreadsheet of various data, including names, simple yes/no
entries and DATES. It is with the dates (regardless of how they formatted
to appear) that I am having difficulty with. I need to sum the number of
times a date (eg. any day in April 2002) appears in a particular column.

name1 surname1 21/04/2002
name2 surname2 02/04/2002
name3 surname3 03/03/2001
name4 surname4 17/04/2002
"" this is where there needs to appear "3"

I have tried every variation of COUNTIF, COUNT, SUM, etc, etc.

Please help. Thankyou.
Gosia

Gosia.Olszewski@uts.edu.au

COUNTIF (for counting) and SUMIF (for summing) cannot work with multiple conditions. Multiconditional counting or summing requires other means: SUMPRODUCT, array-entered formulas, database functions like DCOUNT or DSUM.

=COUNTIF(Range,MONTH(date-range)=4) [ illegal ]

will not work, simply because the condition does not evaluate to a scalar (that is, a single value).

On the other hand,

=SUMPRODUCT((MONTH(date-range)=4)+0)

will give you the desired count of dates falling in April.

You can also put 4 in a cell of its own and use that cell's ref in the above formula.

If interested, you can find many threads in the archives of the old board, involving multiconditional counting and summing.

Aladin

[golszews]

Aladin

Thankyou immensly. At last, it is working! Could you possibly let me know how I could include a particular year? For instance if my set of data spanned a 5 year period, and I only needed April 2002.

Thanx again
gosia