SUMPRODUCT count how many in a range that matches multiple keywords

**tomlancaster** · 10-29-2012, 07:09 AM

Hi - I was wondering if it's possible to use the SUMPRODUCT or similar function to count the number of records in a range that contain certain keywords? The keywords are in Range D10:E10 - The formula works if I just reference one cell but I have multiple criteria I need to search for.

Please Login or Register  to view this content.

Any help or suggestions would be appreciated!

**:) Sixthsense :)** · 10-29-2012, 07:19 AM

Originally Posted by tomlancaster

Please Login or Register  to view this content.

I have not gone through your formula in depth, since we don't know the range reference of named ranges (Active_Month), but a quick suggestion is that you have mentioned your excel version as 2003 in your profile and using the sumproduct on whole column range (Like A:A) wont work in 2003 excel version.

**tomlancaster** · 10-29-2012, 07:35 AM

Many thanks for your reply - My apologies for confusion but I am using MS Excel 2010.

The Active_Month contains a date (e.g. 01/01/2012) but this is convered in "MMMM" format as my sheets are named as Months (e.g. December)

The formula works fine if I just reference one cell in this section of the formula:

"ISNUMBER(FIND(D10,OFFSET(INDIRECT........"

Cell: D10 contains a word like "INC"
Cell E10 contains a word like "HOL"
I'd like to reference D10:E10 so I can search the range for text containing "INC" or "HOL" etc.

**JosephP** · 10-29-2012, 08:13 AM

is your last range meant to be 31 columns? that is what is causing the problems-if it's one column, then your formula will search for both values D10 and E10 in that column.