Sumproduct with a Like condition

xl_psychic

Active Member
Joined
Jan 4, 2004
Messages
359
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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Active Member
Joined
Jan 4, 2004
Messages
359
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.
 

Forum statistics

Threads
1,181,102
Messages
5,928,065
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top