COUNTIF dilemma

scotbyte

New Member
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.

================
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.

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
I couldn't do it without an extra column...but it works:
Book1
ABCD
111/1/2001456 Count
212/1/2001123 2
31/1/20021581
42/1/2002164
53/1/2002195
64/1/200275
75/1/200215018
86/1/200294
97/1/200226
108/1/200284
118/10/200215432
128/25/2002105
138/21/200215834
148/22/20021584634
158/23/200250
168/24/200251
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

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

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.

================
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.

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)

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.

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

Replies
2
Views
110
Replies
14
Views
163
Replies
5
Views
121
Replies
1
Views
51
Replies
2
Views
108

Forum statistics

1,203,491
Messages
6,055,727
Members
444,814
Latest member
AutomateDifficulty

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back