COUNTIF using WEEKDAY
 

Hi,
I wish to find out how many times "1.8" (column J) appears each week of the
year. Dates are in column B.
I currently have a formula which returns the total count of entries per week
but I wish to split this down yet further.

Current formula:

SUMPRODUCT(--(1+INT((B$8:B$9997-(DATE(YEAR(B$8:B$9997),1,2)-WEEKDAY(DATE(YEAR(B$8:B$9997),1,1))))/7)=22))

This returns the number of entries for week 22.

Any ideas?

--
tia

Jock

COUNTIF using WEEKDAY
 

Hi

Like this?

SUMPRODUCT(--(1+INT((B$8:B$9997-(DATE(YEAR(B$8:B$9997),1,2)-WEEKDAY(DATE(YEAR(B$8:B$9997),1,1))))/7)=22)*(J$8:J$9997=1.8))

See at the end of the formula.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Jock" wrote:

Hi,
I wish to find out how many times "1.8" (column J) appears each week of the
year. Dates are in column B.
I currently have a formula which returns the total count of entries per week
but I wish to split this down yet further.

Current formula:

SUMPRODUCT(--(1+INT((B$8:B$9997-(DATE(YEAR(B$8:B$9997),1,2)-WEEKDAY(DATE(YEAR(B$8:B$9997),1,1))))/7)=22))

This returns the number of entries for week 22.

Any ideas?

--
tia

Jock

COUNTIF using WEEKDAY
 

Thanks, that worked ok.


Jock


"Wigi" wrote:

Hi

Like this?

SUMPRODUCT(--(1+INT((B$8:B$9997-(DATE(YEAR(B$8:B$9997),1,2)-WEEKDAY(DATE(YEAR(B$8:B$9997),1,1))))/7)=22)*(J$8:J$9997=1.8))

See at the end of the formula.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Jock" wrote:

Hi,
I wish to find out how many times "1.8" (column J) appears each week of the
year. Dates are in column B.
I currently have a formula which returns the total count of entries per week
but I wish to split this down yet further.

Current formula:

SUMPRODUCT(--(1+INT((B$8:B$9997-(DATE(YEAR(B$8:B$9997),1,2)-WEEKDAY(DATE(YEAR(B$8:B$9997),1,1))))/7)=22))

This returns the number of entries for week 22.

Any ideas?

--
tia

Jock