Sumif from pivot table by criteria (Greater Than)
'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)
<tbody>
</tbody>
'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 |
<tbody>
</tbody>