Below are 2 Array Formulas that do not produce the same results from the data when using a Pivot Table.
They are close but 2 records are being counted/sum’d incorrectly.
I have attached a sample file.
The formulas use the following Range Names:
OppN = Range Name for the Operation Number column on the Data worksheet
Split = Range Name for the Cost column on the Data worksheet
District = Range Name for the District Name column on the Data worksheet
Cat = Range Name for the Category column on the Data worksheet
Formula #1:
This formula should count the unique Operation Numbers (OppN) if the Cost (Split) of the Operation Number is between 50000 – 749999 and if the District is F1, F2, Metro, HQ & South and the Category equals H1 or S1.
{=SUM(--(FREQUENCY(IF((SUMIF(OppN,OppN,Split)>=500000)*(SUMIF(OppN,OppN,Split)<=750 000)*((District="F1")+(District="F2")+(District="Metro")+(District="HQ")+(D istrict="South"))*((Cat="H1")+(Cat="S1")),MATCH(OppN,OppN,0)),ROW(INDIRECT( "1:"&ROWS(OppN))))>0))}
Formula #2:
This formula should Sum the Cost column (Split) for the unique Operation Numbers (OppN) if the cost of the Operation Number is between 50000 – 749999 and if the District is F1, F2, Metro, HQ & South and the Category equals H1 or S1.
{=SUM(IF((SUMIF(OppN,OppN,Split)>=500000)*(SUMIF(OppN,OppN,Split)<=750000)* ((District="F1")+(District="F2")+(District="Metro")+(District="HQ")+(Distri ct="South"))*((Cat="H1")+(Cat="S1")),Split))}
Any idea why these formulas are not functioning correctly?
Thanks...
JimS
