I need to follow up on this formula, because it's working; sort of.

Please forgive me for not providing a sample workbook, I hope this is clear enough

I've added one other set of possible criteria to the end of the formula:

=SUMPRODUCT(COUNTIFS(INDIRECT("Data!B2:B100000"),">="&BeginningThisYear,INDIRECT("Data!B2:B100000"),"<"&NextMonth,INDIRECT("Data!J2:J100000"),InjuryTypes,INDIRECT("Data!Z2:Z100000"),SalesMarketing))

The name SalesMarketing consists of several different items, and should return 13, yet I'm only getting 2 in response. I've been racking my brain and nothing's making sense.


Here's another example, and I can be a bit more specific I think:

Main should show 18, not 15. The named range "GroupABC" consists of {"Group A"; "Group B"; "Group C"}

When I use the formulas individually, the results come back proper, (12, 5, 1), as below, but when I use the named range it just doesn't work.






Main - 15 =SUMPRODUCT(COUNTIFS(INDIRECT("Data!B2:B100000"),">="&BeginningThisYear,INDIRECT("Data!B2:B100000"),"<"&NextMonth,INDIRECT("Data!J2:J100000"),InjuryTypes,INDIRECT("Data!Z2:Z100000"),GroupABC))

Group A - 12 - =SUMPRODUCT(COUNTIFS(INDIRECT("Data!B2:B100000"),">="&BeginningThisYear,INDIRECT("Data!B2:B100000"),"<"&NextMonth,INDIRECT("Data!J2:J100000"),InjuryTypes,INDIRECT("Data!Z2:Z100000"),"Group A"))

Group B - 5 - =SUMPRODUCT(COUNTIFS(INDIRECT("Data!B2:B100000"),">="&BeginningThisYear,INDIRECT("Data!B2:B100000"),"<"&NextMonth,INDIRECT("Data!J2:J100000"),InjuryTypes,INDIRECT("Data!Z2:Z100000"),"Group B"))

Group C - 1 - =SUMPRODUCT(COUNTIFS(INDIRECT("Data!B2:B100000"),">="&BeginningThisYear,INDIRECT("Data!B2:B100000"),"<"&NextMonth,INDIRECT("Data!J2:J100000"),InjuryTypes,INDIRECT("Data!Z2:Z100000"),"Group C"))