Sumproduct with a Like condition

[xl_psychic]

Gurus:

I am trying to build a sumproduct function which would help me count the total number of active people belonging to a specific group of code.

The only twist is this group code would have variants, for eg: TRGB21, TRGB22, TRGB23 etc.

I tried entering the following formula to count the number of active people in all training batches,

=SUMPRODUCT(('Att Details'!D:D=C6&"*")*('Att Details'!AK:AK="Active"))

where C6 houses the value TRGB

I get a #NUM! error while trying this formula.

=COUNTIF('Att Details'!D:D,C6&"*") gives me the figure but does not take the active/inactive status into consideration.

Help appreciated.

 

[barry houdini]

Try [edited]

=SUMPRODUCT((LEFT('Att Details'!D1:D100,LEN(C6))=C6)*('Att Details'!AK1:AK100="Active"))

Note you can't use whole column references like AK:AK

 

[xl_psychic]

Barry:

Many thanks for your swift reply.

This is a good option and works as intended.

However is there anyway in which the ranges cannot be kept constant for an "x" no. of rows only. Not being able to do so will need the users to be careful about the range being used on the other sheet.

Help appreciated.