I've tried all possible ways, sum if, sum product, index an match, etc. but whenever I go back and filter its also picking up the grand total number located in the pivot table.
Therefore in lieu of the correct number (example $17,393) it doubles (example $34,786)
The current formula I'm using to pull the pivot table data into a sheet named "Chart" - Cell Reference (G10)
=SUMPRODUCT(SUBTOTAL(9,OFFSET(DEBTOR!N5,ROW(DEBTOR!N5:N32)-ROW(DEBTOR!N5),,1)),--(DEBTOR!J5:J32>0))
also tried,
=SUMIFS(DEBTOR!$N$5:$N$30,DEBTOR!$J$5:$J$30,">0")
The date in the pivot table (DEBTOR)
Row Labels Sum of Civil Dismissal Sum of Civil New Filing Sum of Civil Suit Sum of Judgements Sum of Chapter 7 Sum of Federal Tax Lien Sum of City Tax Lien Sum of Small Claims Judgement Sum of State Tax Lien Sum of State Tax Warrant Min of Filing Date: Max of Filing Date: Sum of Amount:$ CONOCOPHILLIPS CO2 0 1 0 0 0 0 0 0 0 0 11/13/2006 11/13/2006 $0 FEDERAL INSURANCE CO OF INDIANA2 0 1 0 0 0 0 0 0 0 0 2/9/2007 2/9/2007 $61,022 PERFORMANCE CONTRACTING INC3 1 1 0 0 0 0 0 0 0 0 8/26/1996 8/26/1996 $0 PERFORMANCE CONTRACTORS INC10 0 1 0 0 0 0 0 0 0 0 4/5/2006 4/5/2006 $0 PERFORMANCE CONTRACTORS INC11 0 4 0 0 0 0 0 0 0 0 4/11/2006 2/15/2008 $0 PERFORMANCE CONTRACTORS INC13 0 1 0 0 0 0 0 0 0 0 3/31/2008 3/31/2008 $0 PERFORMANCE CONTRACTORS INC14 0 1 0 0 0 0 0 0 0 0 6/30/2006 6/30/2006 $0 PERFORMANCE CONTRACTORS INC15 0 1 0 0 0 0 0 0 0 0 7/5/2006 7/5/2006 $0 PERFORMANCE CONTRACTORS INC16 0 1 0 0 0 0 0 0 0 0 1/18/2008 1/18/2008 $0 PERFORMANCE CONTRACTORS INC17 0 4 0 0 0 0 0 0 0 0 6/5/2008 9/8/2008 $0 PERFORMANCE CONTRACTORS INC19 0 1 0 0 0 0 0 0 0 0 4/4/2007 4/4/2007 $0 PERFORMANCE CONTRACTORS INC20 0 1 0 0 0 0 0 0 0 0 6/11/2007 6/11/2007 $0 PERFORMANCE CONTRACTORS INC21 0 1 0 0 0 0 0 0 0 0 5/2/2008 5/2/2008 $0 PERFORMANCE CONTRACTORS INC22 0 1 0 0 0 0 0 0 0 0 1/21/2005 1/21/2005 $0 PERFORMANCE CONTRACTORS INC23 0 1 0 0 0 0 0 0 0 0 3/13/2008 3/13/2008 $0 PERFORMANCE CONTRACTORS INC24 0 1 0 0 0 0 0 0 0 0 7/13/2005 7/13/2005 $0 PERFORMANCE CONTRACTORS INC25 0 0 0 0 0 0 0 0 1 0 2/6/1996 2/6/1996 $579 PERFORMANCE CONTRACTORS INC26 0 0 0 0 0 0 0 0 1 0 3/11/1996 3/11/1996 $381 PERFORMANCE CONTRACTORS INC27 0 0 0 0 0 0 0 1 0 0 3/27/1996 3/27/1996 $2,432 PERFORMANCE CONTRACTORS INC28 0 1 0 0 0 0 0 0 0 0 7/18/1995 7/18/1995 $6,215 PERFORMANCE CONTRACTORS INC29 0 2 0 0 0 0 0 0 2 0 4/29/2008 5/30/2013 $0 PERFORMANCE CONTRACTORS INC30 0 1 0 0 0 0 0 0 0 0 5/2/2005 5/2/2005 $0 PERFORMANCE CONTRACTORS INC31 0 0 0 0 0 0 0 0 1 0 8/31/2010 8/31/2010 $273 PERFORMANCE CONTRACTORS INC32 0 0 0 0 0 0 0 0 1 0 10/3/2012 10/3/2012 $17,393 PERFORMANCE CONTRACTORS INC5 2 4 0 0 0 0 0 0 0 0 8/5/2003 4/28/2005 $0 PERFORMANCE CONTRACTORS INC6 1 1 0 0 0 0 0 0 0 0 11/8/2005 11/8/2005 $0 PERFORMANCE CONTRACTORS INC8 1 0 0 0 0 0 0 0 0 0 9/8/2005 9/8/2005 $0 PERFORMANCE CONTRACTORS INC9 0 1 0 0 0 0 0 0 0 0 9/21/2005 9/21/2005 $0 Grand Total 5 32 0 0 0 0 0 1 6 0 7/18/1995 5/30/2013 $88,295
Reply With Quote