Need formula that will work on rows barring subtotal and Grandtotal of Pivot table result.

[Kumar Shanmugam]

I have attached the pivot table result data. In the result, there are subtotal of one location and agency personal. in AJ column I want the sum of values > 9 in range D3:AH3. When I apply the formula, it also calculates values > 9 in range of D3:AH3 of subtotal and Grand total value which is not correct. Please find attached the sample data.

 

[xlstime]

As per my knowledge there is only one way to ignore/bypass Total/GrandTotal but for this you have to change your formula with subtotal or AGGREGATE.

Than you can use AGGREGATE function

AGGREGATE(function_num, options, array, [k])

function_num = number of function for sum = 9
options = 3 to skip Subtotal , AGGREGATE and errors

I have attached the pivot table result data. In the result, there are subtotal of one location and agency personal. in AJ column I want the sum of values > 9 in range D3:AH3. When I apply the formula, it also calculates values > 9 in range of D3:AH3 of subtotal and Grand total value which is not correct. Please find attached the sample data.

 

[Kumar Shanmugam]

Can you tell me sample how to do it. I understood
function_num = number of function for sum = 9
options = 3 to skip Subtotal , AGGREGATE and errors

 

[xlstime]

Please refer enclosed sample sheet.

Can you tell me sample how to do it. I understood
function_num = number of function for sum = 9
options = 3 to skip Subtotal , AGGREGATE and errors

 

[Kumar Shanmugam]

Hi As per the data provided by you, You are applying formula on subtotal rows. This formula do not serves the purpose what I want.