COUNTIF dilemma

[scotbyte]

I am stuck with a problem that I think COUNTIF could solve, but when I try typing my formula it it gives me an error or returns a 0 result.

THE PROBLEM/TASK
================
Column A contains a date value. I want to count how many of the dates are equal to today's week number, using the WEEKNUM(Today()) function.

Column B contains a "call number". The above mentioned countif function would have to if possible discount any "call number" that does not begin with "15"

I can solve this dilemma by creating a column C that calculates the week number from column A and then use a simple countif instruction to calculate how many calls for this week.

Can anyone help, please

 

[Corticus]

I couldn't do it without an extra column...but it works:

Book1
	A	B	C	D
1	11/1/2001	456		Count
2	12/1/2001	123		2
3	1/1/2002	158	1
4	2/1/2002	164
5	3/1/2002	195
6	4/1/2002	75
7	5/1/2002	150	18
8	6/1/2002	94
9	7/1/2002	26
10	8/1/2002	84
11	8/10/2002	154	32
12	8/25/2002	105
13	8/21/2002	158	34
14	8/22/2002	15846	34
15	8/23/2002	50
16	8/24/2002	51
17
...

HTH,
Corticus

I would also like to know how to do this count without a C column
This message was edited by Corticus on 2002-08-21 08:31

 

[scotbyte]

Yes it works great with an extra column, but the drawback is that if someone wants to insert an extra row in the middle of the sheet then you have to manually copy the formulas into the appropriate cells to obtain the correct counts

 

[Aladin Akyurek]

On 2002-08-21 07:59, scotbyte wrote:
I am stuck with a problem that I think COUNTIF could solve, but when I try typing my formula it it gives me an error or returns a 0 result.

THE PROBLEM/TASK
================
Column A contains a date value. I want to count how many of the dates are equal to today's week number, using the WEEKNUM(Today()) function.

Column B contains a "call number". The above mentioned countif function would have to if possible discount any "call number" that does not begin with "15"

I can solve this dilemma by creating a column C that calculates the week number from column A and then use a simple countif instruction to calculate how many calls for this week.

Can anyone help, please

If I understand you correctly, you are using something like

=COUNTIF(C2:C50,WeekNumber)

where C2:C50 houses the formula

=WEEKNUM(A2)

since A2:A50 houses true dates.

If so, you could set up the condition/criterion differently and still use COUNTIF:

Let E1 house the start date of the week of interest and E2 the end date of the same week (in your case E2 houses today's date).

=COUNTIF(A2:A50,">="&E1)-COUNTIF(A2:A50,">"&E2)

If the week of today's date is the target week, the above reduces to:

=COUNTIF(A2:A50,">="&E1)

 

[scotbyte]

Yes this solution would work and I would not have any extra columns. I would be able to insert extra rows in the middle of the spreadsheet without having to copy formulas into the blank cells.

Only drawback with this one is how to automatically calculate the start and end dates of the current week. My spreadsheet is used to record all calls but only display running totals for the current week and the previous week. Should I require stats for older weeks I can extract these manually.

 

[scotbyte]

Ignore some of my last comments, calculating the start and end dates for the week is very easy (today less the weekday-1).

You have given me the solution I was seeking

Thanks