Sumproduct not working

I'm trying to get a total from my using a Sumproduct formula. In Sheet2 is where the data is that I'm trying to extract. On Sheet2 column I through T is where the Month totals are that I'm trying to extract. I get a result of #VALUE!.

=SUMPRODUCT(--(Sheet2!$C:$C=$A$2),--(Sheet2!$E:$E=$B2),--(Sheet2!$I:$T=$C2),(Sheet2!I:T))

Name	ID#	Date
Tracey	123456	October

Thank you.

Will you please attach a sample Excel workbook? Please don't attach a picture of one. Especuially one that contains no labels. I have no idea if your picture is the raw data or the desired result!!!

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Ooops. posted twice..

The main reason the SUMPRODUCT formula fails is that all the arrays in the formula have to be the same size. What you need to do is find out where the month is, and define a range that contains the same number of rows as the other criteria in this column. It will probably make more sense with an example:

To find out where Oct occurs, Use =MATCH(C2,Sheet2!1:1,0) - This is Cell F2 in my example and the result is 17. Oct is the 17th column on the spreadsheet.

To define a range of cells, now that we know the column use OFFSET(Sheet2!A:A,0,F2-1) - This is part of the formula in cell G2. It means take the range A:A on sheet2, shift it down 0 rows and move it over F2-1 columns. The reason for the minus 1 is that Match starts counting at 1 but offset starts counting at zero. So go to Column A and go 16 columns over.

For some reason, when I put this in SUMPRODUCT it doesn't work, but SUMIFS does work. So the formula in Cell G2 is =SUMIFS(OFFSET(Sheet2!A:A,0,F2-1),Sheet2!C:C,A2,Sheet2!E:E,B2).

I took the formula in F2 and copied and pasted into this formula to get: =SUMIFS(OFFSET(Sheet2!A:A,0,MATCH(C2,Sheet2!1:1,0)-1),Sheet2!C:C,A2,Sheet2!E:E,B2) - This formula eliminates the helper column.

To John's point. I did another version of the spreadsheet using an Excel Table. Not only is the formula easier to read, but it reduces the amount of work Excel has to do by about a factor of 100,000 in this case.

The fix was easy enough the offset has to be from the first column of the table: =SUMIFS(OFFSET(Table1[Forecast Period (Cal],0,MATCH(C5,Table1[#Headers],0)-1),Table1[Material],A5,Table1[ID],B5) You were a couple of columns over so you got the answer from a couple of columns over.

Here is the quick and dirty tutorial on OFFSET. It's kind of like a treasure map: you start at a place, take so many paces forward and so many paces to the right, and the dig down so deep.

To describe a range in terms of a cell = OFFSET(Start_Cell, Go Down, Go Right, Number Rows, Number of Columns)

So =OFFSET(A1,1,2,3,4) means start in cell A1, go 1 row down (To A2), go 2 columns over (to C2) and give me a range 3 rows deep and 4 columns wide (C2:E5). Any of the parts can be computed so this makes the range dynamic.

If you already have a range and you want to define another range in terms of it: =OFFSET(Original Range, Go Down, Go Right).

So =OFFSET(A1:A10,0,1) means B1:B2. This is what I am using here.

OF course to go up or left, use negative numbers. As long as you stay on the page, you should not get an error.

Originally Posted by dflak

The main reason the SUMPRODUCT formula fails is that all the arrays in the formula have to be the same size.

Not necessarily. Depends on what you're wanting to do.

For example:

Data Range

	A	B	C	D
1		Region1	Region2	Region3
2	North	27	21	97
3	North	17	92	82
4	South	70	84	40
5	West	19	22	38
6	West	5	95	2
7	North	96	81	94
8	East	23	30	24
9	East	13	62	85
10	North	56	37	3
11
12	North	703

Get the sum from all regions associated with North.

=SUMPRODUCT((A2:A10=A12)*B2:D10)

The arrays are different sizes.

I meant same number of rows. =SUMPRODUCT((A2:A10=A12)*B2:D12) wouldn't work because there are rows without something to match up to.