Countifs

[JOEE1979]

Cell (C4) has formula

=COUNTIFS(I7:I7326, MONTH($L$1) = MONTH(I7:I7326), C7:C7326,"YES")

Cell (L1)

=TODAY()

Not sure why I am getting "SPILL!"

Schedule 4 Tracker - Copy12313.xlsx
	C	D	E	F	G	H	I
7	YES			OSH	1-Jan-24	31-Oct-24	1-Nov-24
2024

Cell Formulas
Range	Formula
G7	G7	=A7

Cells with Data Validation
Cell	Allow	Criteria
C7:D7326	List	YES
E7:E7320	List	PMA, UNDER
F7:F7320	List	OSH, STVL, SRK, EG
H7:I7	List	='Current Month'!$AL$26:$AL$29

 

[StephenCrump]

You're getting #SPILL! because criteria1 in your COUNTIFS is an array.

COUNTIFS is the wrong function to use here, as it will only accept range arguments: criteria_range1, criteria_range2 etc.

One option you could use instead: =SUM((MONTH(I7:I7326)=MONTH(L1))*(C7:C7326="YES"))

 

[JOEE1979]

You're getting #SPILL! because criteria1 in your COUNTIFS is an array.

COUNTIFS is the wrong function to use here, as it will only accept range arguments: criteria_range1, criteria_range2 etc.

One option you could use instead: =SUM((MONTH(I7:I7326)=MONTH(L1))*(C7:C7326="YES"))

That worked, however I needed to go a bit further and make it into weekly, so I replaced MONTH with WEEKNUM, but it gave me a value error

 

[StephenCrump]

Can you post your formula/layout so we can take a look?

 

[JOEE1979]

Can you post your formula/layout so we can take a look?

=SUM((WEEKNUM($I7:$I7326)=WEEKNUM($L$1))*(C7:C7326="YES"))

 

[StephenCrump]

Try changing this part of the formula:

WEEKNUM($I7:$I7326)

to

WEEKNUM(--$I7:$I7326)

The double unary (--) is one way in Excel to coerce values to numeric (you could also add 0, or multiply by 1).

It shouldn't be necessary here, as the input is already numeric. It's just a quirk of this function (and others like EDATE and EOMONTH) that this is what you need to do to make it work.

 

[JOEE1979]

Try changing this part of the formula:

WEEKNUM($I7:$I7326)

to

WEEKNUM(--$I7:$I7326)

The double unary (--) is one way in Excel to coerce values to numeric (you could also add 0, or multiply by 1).

It shouldn't be necessary here, as the input is already numeric. It's just a quirk of this function (and others like EDATE and EOMONTH) that this is what you need to do to make it work.

Worked, Thanks