Using sumproduct when array contains some cells with text

Hi guys,

Please see attached work book

Sheet 1 contains my sumproduct formula which is error-ing due to the array containing some cells with text.
Sheet 2 contains the data that I am trying to sum along with the criteria arrays

The issue is that my data is broken into tables that are presented and subtotalled monthly so it contains cells with text below each month and also the text in the headers of the next months tables etc etc.

I am trying to do a sumproduct for the entire years data with the following formula

SUMPRODUCT((Sheet2!D3:P31)*(Sheet2!D1:P1=Sheet1!B1)*(Sheet2!A3:A31=Sheet1!A2))

Note - The array 'Sheet2!D3:P31' spans across headings and other cells containing text

So I know this formula will not work but I am hoping there is away to ignore the cells with text and still sum the numerical data as I still need to present the data in a month by month table.

Thanks, Mike

it would be 1000.

The main thing I want is to access the array across Dec-16 to Dec-17 as opposed to creating a separate sumproduct formula for each month. Maybe I shouldn't be using sumproduct at all, I just need to be able to access that entire data set even though it contains some cells with text in it. Note - Obviously the workbook I uploaded is a mock up of the real workbook and only shows Dec-17 to Jan-17.

Sorry meant to say Dec-16 to Jan-17

From D3. Sorry i thought that would be apparent when you looked at the formula. I have uploaded another version showing the criteria range for the month and the criteria range for the type and the area in which the formula would be summing. Hopefully this is enough for you.

Mike

Try this array formula (must be enter with CTRL+SHIFT+ENTER)

Formula:

Please Login or Register  to view this content.

See the file

Nice one, so all I needed to use is iferror? I always thought your array couldn't contain text and numerical values??