PIVOT TABLE - PARTIAL FIELD REPORT FILTER

Question

I have a sample table like:

Client	Product	Price
Client1	P3_New	$206
Client2	P3_New	$593
Client3	P3_New	$362
Client4	P3_Expired	$523
Client5	P3_Old	$758
Client6	P1_Old	$473
Client7	P2_New	$861
Client8	P2_Old	$172
Client9	P2_New	$816
Client10	P3_Expired	$785
Client11	P2_New	$661
Client12	P1_New	$548
Client13	P2_Old	$662
Client14	P2_Old	$937
Client15	P3_Expired	$55
Client16	P1_Expired	$24
Client17	P3_Expired	$88
Client18	P1_Expired	$943
Client19	P2_Old	$574
Client20	P1_Old	$185

Can I have a Pivot Table as follows filtering the data:

Product	Price
P1	2,173
P2	4,683
P3	3,370

without having an additional column being inserted in the source data range showing the first two characters of Product Column?

Thanx in advance.

Answer 1

Hi,

If your data is large, it is possible to solve this problem by writing an SQL query in MS Excel.  Do let me know if you are interested in that solution.

Also, how may rows in your live data?

Answer 2

Yar Ashish,

Already have a good idea of SUMPRODUCT, thanx 2 Jacob Sakaria. Unfortunately, that was a sample only but actually have quite a king size data, in which case applying a formula shall lead to have the workbook hang for minutes upon simple changes as well!

Guess, adding an additional column is the only way out?

Thanx anyway buddy!

Answer 3

Hi,

No this cannot be done via a pivot table.  Instead you may try the following formula and copy down

=SUMPRODUCT((LEFT($D$4:$D$23,2)=H4)*($E$4:$E$23))

H4 has P1