Pivot table question

[ChristineJ]

My worksheet has data with column labels.
Column C - Principal
Column G - Interest
Column F - Reason
(Column F rows may contain "Machine Purchase", "On Account", or "Cash Advance" - each appears repeatedly in the column.)

=SUMIFS(G2:G131,F2:F131,"Machine Purchase",C2:C131,">75000") gives the correct result of 48,024

Is there a way to extract this result in a pivot table? I dragged the Reason field in the Rows area so there is a list of the three possible entries in column F. Is it possible to extract the total interest for each of the three, but only for the rows in the data set in Column C are > 75,000?

Thanks!

 

[Alex Blakenburg]

Not unless your table is also at the underlying data's row level which makes the Pivot unnecessary.
To get what you want some options are:

• Have a helper column in the underlying data identifying the rows you want ie exceed $ 75k or maybe even $ brackets.
  Then use that field as a page filter
• Use Power Query either instead of a pivot or to feed the pivot
• Use Power Pivot and the data model and use Measures to get the result you need.

 

[ChristineJ]

This is very helpful. I am using what you have in your first option and it works fine. I'm also just starting to use pivot tables quite a bit want to be sure I am not missing something on how to use them.

I appreciate your response. Thanks!

 

[Alex Blakenburg]

I used to use helper columns a lot. In case it helps give you some more ideas, here is just a quick view of some possibilities.
Vlookup with True helps you group things.
If you are not using Excel Tables I strongly encourage you to use them.
As a data source to a pivot table they automatically expand so you don't have to keep expanding your pivot tables data range.
As a source for a Lookup table, the lookup will automatically include any additional rows.

Book8
	C			F	G	H	I	J	K	L	M
1	Principal			Reason	Interest	Grouping	Threshold			Grouping	Category
2	10,000			Reason1	1000	Low				-	Low
3	12,000			Reason2	1200	Low				20,000	Mid
4	21,000			Reason3	2100	Mid				60,000	High
5	50,000			Reason4	5000	Mid				100,000	Very High
6	58,000			Reason5	5800	Mid
7	60,000			Reason6	6000	High
8	70,000			Reason7	7000	High				Threshold	75,000
9	80,000			Reason8	8000	High	Greater than 75,000
10	90,000			Reason9	9000	High	Greater than 75,000
11	110,000			Reason10	11000	Very High	Greater than 75,000
12
13
14
15				Threshold	Greater than 75,000
16
17				Row Labels	Sum of Interest
18				High	17000
19				Very High	11000
20				Grand Total	28000
Sheet1

Cell Formulas
Range	Formula
G2:G11	G2	=0.1*C2
H2:H11	H2	=VLOOKUP(C2,$L$2:$M$5,2,TRUE)
I2:I11	I2	=IF(C2>=$M$8,"Greater than " &TEXT($M$8,"#,##0"),"")

 

[ChristineJ]

Thanks - this is excellent advice. I have been using the grouping capability in a number of cases.

I agree with you on using tables - far more efficient. For what I am doing now, however, the data set will not change at all. In addition, I'm writing accounting problems for introductory students, and most of them have limited Excel experience, so I'm trying to stick to having them use basic cell references in their formulas. A table might be too advanced right now....

Appreciate your feedback! C