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.
Last edited by yuenk; 12-22-2016 at 12:11 PM.
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.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Ooops. posted twice..
I've added an attachment to my 1st post
Try
=SUMPRODUCT((Sheet2!$C2:$C10=$A$2)*(Sheet2!$E2:$E10=$B2*(Sheet2!$I1:$T1=$C2)*(Sheet2!I2:T10)))
Note: you had two errors:
Wrong name and wrong month (October instead of Oct)
Do not use whole column ranges with SUMPRODUCT as it will evaluate over 1 million rows.
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.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Originally Posted by dflak
Your example worked but when I applied to my file I'm always getting the result in the table column K. On sheet1 row5 no matter what month I change in column C it will always return the value from Table 1 column K. Ive attached the file you sent me that I tried applying myself so you can see.
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.
For the life of me I couldnt figure that out! Thanks dflak for taking your time and helping me out!
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.
Not necessarily. Depends on what you're wanting to do.
For example:
Data Range
Get the sum from all regions associated with North.
=SUMPRODUCT((A2:A10=A12)*B2:D10)
The arrays are different sizes.
Last edited by Tony Valko; 12-22-2016 at 06:10 PM.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks