PDA

View Full Version : Sumif from pivot table



Loss1003
01-16-2018, 02:51 PM
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

Loss1003
01-16-2018, 03:01 PM
copy of the file attached. Thanks