http://chandoo.org/forums/topic/dynamic-sumif-formula-using-column-and-row-filtering Ask Excel and Charting Questions, Muse about Visualizations, Learn and Share - Discussion Forums - Chandoo.org en-US Tue, 21 May 2013 08:29:20 +0000 http://bbpress.org/?v=1.0.1 q http://chandoo.org/forums/search.php http://chandoo.org/forums/topic/dynamic-sumif-formula-using-column-and-row-filtering#post-6128 Fri, 11 Feb 2011 14:38:07 +0000 Luke M 6128@http://chandoo.org/forums/ <p>SUMPRODUCT explained by Chandoo:<br /> <a href="http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/" rel="nofollow">http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/</a> </p> http://chandoo.org/forums/topic/dynamic-sumif-formula-using-column-and-row-filtering#post-6108 Thu, 10 Feb 2011 23:16:55 +0000 barmacost 6108@http://chandoo.org/forums/ <p>Thanks Luke...worked great. Now I just need to figure out how to interpret it. Haven't used sumproduct all that much before.</p> <p>Thanks again. </p> http://chandoo.org/forums/topic/dynamic-sumif-formula-using-column-and-row-filtering#post-6107 Thu, 10 Feb 2011 20:34:28 +0000 Luke M 6107@http://chandoo.org/forums/ <p>Do make sure that your reference cell and column headers are all true numbers and not text. If so, something like this should work:</p> <p>=SUMPRODUCT(--(C2:C100=B2),INDEX(D2:Z100,,MATCH(B1,D1:Z1,0)))</p> <p>In this example, column headers are in D1:Z1, range of data goes down to row 100, B1 is the column header to use, and B2 is the product type to look at. </p> http://chandoo.org/forums/topic/dynamic-sumif-formula-using-column-and-row-filtering#post-6106 Thu, 10 Feb 2011 19:45:15 +0000 barmacost 6106@http://chandoo.org/forums/ <p>I'm stumped!! I've tried Sumif, Sumproduct, Offset, Index and I can't seem to figure out to do this. I have a datasheet with multiple columns each with a date heading. I want to add up the data for a particular column based on a reference cell while filtering the sum based on the product type information in another column. For instance if I have a reference cell in sheet 1 (B1) I want the formula to look for that reference in sheet 2 (row 1) to determine, which column to sum. Once the column is found (say column k for instance) I want it to sum up only those numbers that correspond to a specific product type found in (column c). I can get the results using static references but I can't seem to make the sum range dynamic based on a date reference. </p>