Count Positive and Negative Values in Pivot

[Thomas Kuriakose]

Respected Sirs,

Based on the pivot summary attached, we need to count the number of occurrences which have negative values and number of values which have positive values. I have currently got the result in two columns.

Is there a way to get the result in one column, or is there a better way to arrive at this summary.

What we need to analyze from this data is how many materials have GR & GI in the period and what is their frequency.

Thank you very much,

with regards,
thomas

 

[NARAYANK991]

Hi ,

Can you explain how you want the result in one column ?

If you can explain with reference to a few rows , it will be clear what you are looking for.

Narayan

 

[Thomas Kuriakose]

Respected Sir,

Thank you for your guidance.

If we take material 10011 - row 6 in Summary Sheet,

1. When the pivot is filtered by Type "GI" - the total count for range (C6:F6) in H6 should be= 4 (Values <0).
2. When the pivot is filtered by Type "GR" - the total count for range (C6:E6) in H6 should be = 3 (Values>0).

I tried '=COUNTIFS(C6:F6,">0",C6:F6,"<0") in H6, but it is giving zero.

Thanks,

with regards,
thomas

 

[NARAYANK991]

Hi ,

See this file ; I have deliberately left blank columns in between , since if the pivot table needs to extend in order to accommodate new entries , it needs blank columns.

Narayan

 

[Thomas Kuriakose]

Respected Sir,

This is amazing.

Thank you very much for the solution and the support extended on this.

Much appreciated.

with regards,
thomas