SUMIFS trouble

[JoshLyman]

Not sure if this is just a limitation of SUMIFS (happy to be given the bad news), but I'm struggling to get it to work with a couple of table columns.

I'm trying to use the following formula in cell B1, with the intention of dragging it down so that it'll tell me how many hours have been logged within the named months
=SUMIFS(OTJLog[OTJ Hours],OTJLog[Activity date dd/mm/yyyy],">="&BB21,OTJLog[Activity date dd/mm/yyyy,"<="&EOMONTH(BB21,0)))

DRAFT - OTJ Log - v0.1.xlsm
	A	B
1	Jan-23
2	Feb-23
3	Mar-23
4	Apr-23
5	May-23
6	Jun-23
7	Jul-23
8	Aug-23
9	Sep-23
10	Oct-23
11	Nov-23
12	Dec-23
Sheet8

The table it's drawing it's data from is here

DRAFT - OTJ Log - v0.1.xlsm
	B	C	D	E	F
9	Activity date dd/mm/yyyy	OTJ Hours	Non-OTJ Hours	Short description of the activity e.g., assignments, online learning, practical training, lectures	KSB(s) covered within the activity Add a row for each one covered, where appropriate
10
11
12
13
14
15
16	Total	0	0
3 - OTJ LOG

Cell Formulas
Range	Formula
C16	C16	=SUBTOTAL(109,[OTJ Hours])
D16	D16	=SUBTOTAL(109,[Non-OTJ Hours])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C10:C15	Cell Value	>7	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B10:B15	Date	between 01/01/1900 and 31/12/9999
C10:D15	Custom	=ISNUMBER(C10)
F10:F15	List	=Lookups!$D$2:$D$47

It works if I specify the range (e.g., C10:C15) but not if I try to reference the table column itself. The issue is, the table will expand, a lot, so I can't specify a range without just making the table enormous as a precaution, which I don't really want to do. Is this just a limitation of SUMIFS? Is there another solution, using SUMPRODUCT for example?

 

[SanjayGMusafir]

=SUMIFS(OTJLog[OTJ Hours],OTJLog[Activity date dd/mm/yyyy],">="&BB21,OTJLog[Activity date dd/mm/yyyy,"<="&EOMONTH(BB21,0)))

Try, I think there was some Typo in formula

=SUMIFS(OTJLog[OTJ Hours],OTJLog[Activity date dd/mm/yyyy],">="&BB21,OTJLog[Activity date dd/mm/yyyy],"<="&EOMONTH(BB21,0))

 

[JoshLyman]

Amazing. Something so simple as a missing bracket! Thanks @SanjayGMusafir

 

[SanjayGMusafir]

Glad to help you