SUMPRODUCT - Sum Count of Multiple Criteria met

Hi All,

I am using Excel 2007 for Windows. I would like some assistance with using the SUMPRODUCT Function in a worksheet based formula.

I have three criteria to meet, and then sum the total count of the criteria met. I am using dynamic named ranges (two of them span multiple columns):
Range1 – 8 columns wide, length dynamic, cells contain numerical constants
Range2 – 8 columns wide, length dynamic–same as Range1, cells contain numerical constants
Range3 – 1 column wide, length dynamic–same as Range1, cells contain numerical constants.

Scenario:
Defined range names:
Row 13 is a column header name
Range1 =OFFSET(Sheet1!$H$13,1,0,COUNT(Sheet1!$H:$H),8) - workbook level name
Range2 =OFFSET(Sheet1!$P$13,1,0,COUNT(Sheet1!$P:$P),8) - workbook level name
Range3 =OFFSET(Sheet1!$E$13,1,0,COUNT(Sheet1!$E:$E),1) - sheet level name

Range1 is a store reference, Range2 is a product code, and Range3 is a day of the month.
For example, I would like to know the summed count when store 1 (in Range1) has sold product 223 (in Range2) on 31st day, represented as 31 (in Range3).

Attempted formulas - not working:
=SUMPRODUCT((Range1=1)*(Range2=223)*(Sheet1!Range3=31))

=SUMPRODUCT(--(MMULT((Range1=1)*(Range2=223)*( Sheet1!Range3=31),{1;1;1;1;1;1;1;1})))

The formula will be copied across 60 columns and down 31 rows. I would appreciate the most efficient Function and syntax to meet my requirements.

Hope you can help.

Thank you,
Sam

The first formula works fine in my test. Can you post the workbook?

Hi Bob,

Thank you for reply, and taking the time to test the formula.

Bob Phillips said:

The first formula works fine in my test. Can you post the workbook?

Click to expand...

No, it's a work file and I don't have permission to post it. When I use only two of the three criteria together, in various combinations, I get a total. However, when I use all three of the criteria the formula returns zero. I have used ISNUMBER to test cells in each column to make sure that they are numerical values. Can you think of anything I can check or do?


Thank you,
Sam

Can't you knock up an example workbook that shows the problem?

Bob,

Bob Phillips said:

Can't you knock up an example workbook that shows the problem?

Click to expand...

The formula wasn't returning the expected results because I was using an incorrect range. Thank you for your time.