Sumproduct with multiple criteria including partial search

**Groovicles** · 07-29-2013, 10:51 AM

Hi everyone,

Long time reader, first time poster. I've really been learning a lot about excel from the guru's here but this one has really stumped me:

Column A:A lists a combination of text and numerical values, i.e. RR001030, RR001031, RR004032, RR004035, etc.
Column F:F lists numbers between 1:100
Cell K5 is a drop down list of figures such as RR001, RR004, etc.

What I'm trying to do is count the number of values in column A:A has a value under 80 as listed in column F:F based on an entry (either typing it in or drop down list) in cell K5. I've been able to count the number of values in column A:A based on a type or drop down value entered in cell K5, but I can't seem to filter the results to show only values under 80 as listed in column F:F. Your genius is greatly appreciated.

Thanks!!

**daddylonglegs** · 07-29-2013, 10:56 AM

Hello Groovicles, try this formula

=SUMPRODUCT((LEFT(A2:A100,LEN(K5))=K5)*(F2:F100<80))

adjust ranges as required

**kenny.fsw** · 07-29-2013, 10:57 AM

Hiya mate,

Try this, it should work

Please Login or Register  to view this content.

**Groovicles** · 07-29-2013, 11:32 AM

Wow, that was fast. Thanks very much guys! Daddylonglegs you formula worked perfectly. Kenny.fsw, your formula for some reason returned a #NAME? error. Its strange, I got the same thing with DLL's formula when I used A:A instead of A1:A100. Thanks so much guys!!

**oeldere** · 07-29-2013, 11:38 AM

The reason you get the failure Name is because in the excel 2003 (which is your version according to your profile), the formula countifs don't exist.

In that case you need to rely on sumproduct (=> which daddylonglegs has done).