Help Creating a formula that combines a countif statement and a date filter

[mark.studley]

Hi

Ihave the following statement that calculates the total number of instances the word anglesey occurs in a column.

=COUNTIF(F2:F1000000,"Anglesey")

I have a seperate column for the date. I want to add another cell where i calculate the number of times it occurs in a week. I was just wondering if someone could help me.

Thanks
Mark

[rcm]

Is it a specific week or is it every week.

for a specificweek:

=COUNTIF(F2:F1000000,"Anglesey",g2:g1000000,">="&g1,g2:g1000000,"<="&h1) where g1 is the staring date and h1 is the ending date
for every week

identify each entry with the week number first and then do a table with the week numbers and the frequency

[Pete_UK]

Assuming dates are in column D:

=COUNTIFS(F2:F1000000,"Anglesey",D2:D1000000,">="&start_date,D2:D1000000,"<"&start_date+7)

Use another cell to put your start_date in, then use that cell reference in the formula.

As you have such a large range, you may as well use full column references:

=COUNTIFS(F:F,"Anglesey",D:D,">="&start_date,D:D,"<"&start_date+7)

Hope this helps.

Pete

[mark.studley]

Thanks both, I want it to show data for everyweek. Im not the most experienced on excel, is the start_date a formula?
Im guessing i can then just adjust the formla to say start date+7, to start_date+14 and so on if i use that option?

Alternatively would you mind explaining the table option?

[Pete_UK]

start_date in my formula is just a cell, say M2, so you can put your first date in there and in M3 you can have the formula:

=M2+7

and copy this down to give you successive weekly dates. Then the formula that I gave you would become:

=COUNTIFS(F:F,"Anglesey",D:D,">="&M2,D:D,"<"&M2+7)

(maybe in N2), so that you can then copy that down as far as you need to. Thus you have a small summary table in columns M and N.

Hope this helps.

Pete

[mark.studley]

That is great, thanks for the help pete, much appreciated.