Shorten a Countif/Sumproduct formula

Hi, I have a formula, that I feel like should be shorter:

=COUNTIFS(INDIRECT("Data!B2:B100000"),">"&EndLastYear,INDIRECT("Data!B2:B100000"),"<="&ThisMonth,INDIRECT("Data!J2:J100000"),"Value A")+COUNTIFS(INDIRECT("Data!B2:B100000"),">"&EndLastYear,INDIRECT("Data!B2:B100000"),"<="&ThisMonth,INDIRECT("Data!J2:J100000"),"Value B")+COUNTIFS(INDIRECT("Data!B2:B100000"),">"&EndLastYear,INDIRECT("Data!B2:B100000"),"<="&ThisMonth,INDIRECT("Data!J2:J100000"),"Value B")

I was trying to use a sumproduct, somewhere, but couldn't seem to figure it out. Any help would be awesome! (Also, would there be a way to have a named range for the list of "Values"?)

Thanks, I'll give it a try. Sorry about the indirects (they're a result of overwriting data w/ vba, causing my formulas to disappear)

*worked perfectly. Thanks again!

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"))

Furthermore, when I evaluate the formula for Main, as shown above, the final result it shows me is 11, 4, 0 (as if each result is accurate minus 1), Very very strange.

I found on a website:https://stackoverflow.com/questions/...e-named-ranges


That if I use Transpose in an ARRAY formula it does the trick, but I wouldn't call this ideal, if possible.. Here's the formula I ended up with

{=SUM(COUNTIFS(INDIRECT("Data!B2:B100000"),">="&BeginningThisYear,INDIRECT("Data!B2:B100000"),"<"&NextMonth,INDIRECT("Data!J2:J100000"),InjuryTypes,INDIRECT("Data!Z2:Z100000"),TRANSPOSE(GROUPABC)))}