Sumifs/contifs criteria across multiple columns

[Mikeymike15]

Good morning all,

I need some coding efficiency help here

I need to find a criteria across multiple columns (in this case "TF001")

A B
TF001 TF003
TF002 TF004
TF001 TF001

I've solved my problem using the formula below (essentially repeating a single column formula for each column I want to find the criteria, and then adding them together)

=COUNTIFS(dMod!$K$2:$K$675,"="&$C5,dMod!$G$2:$G$675,">="&H$3,dMod!$G$2:$G$675,"<"&I$3)+COUNTIFS(dMod!$L$2:$L$675,"="&$C5,dMod!$G$2:$G$675,">="&H$3,dMod!$G$2:$G$675,"<"&I$3)

There's gotta be something less redundant

I've tried to condense this array from $K$2:$K$675 to $K$2:$L$675 to clean up my formula but it returns a value #.

What trick am I missing. I read through about a dozen threads before I shot this new thread out.

Thanks,
Mike

 

[circledchicken]

Hi,

Perhaps something like this:

=SUMPRODUCT(
    (dMod!$K$2:$K$675 = $C5) + (dMod!$L$2:$L$675 = $C5),
    --(dMod!$G$2:$G$675 >= H$3),
    --(dMod!$G$2:$G$675 < I$3))

I haven't tested it properly so please check it does what you need.