Frequency Functions has N/A in Data Array Arguement

[legalhustler]

I have #N/A in the data array argument (in bold below) of the Frequency function when I evaluate the formula, which is correct because it's an item that does not match but when it sums it returns a #N/A. The sum should be 4 because there are 4 unique values (1, #N/A, 4, 3). Not sure how to get it sum to 4.

Evaluated formula:

SUM(IF(FREQUENCY({1;1;1;1;#N/A;4;4;3;3;3;#N/A},{1;2;3;4;5;6;7;8;9;10;11}),1))

Original Formula:

SUM(IF(FREQUENCY(MATCH(MID(VAATB,Rules!$I$3,Rules!$G$3),Rules!$E$3:$E$6,0),ROW(VAATB)-ROW($A$2)+1),1)

 

[Matty]

Could you share a small example of your data so that we can establish what's going on?

Matty

 

[legalhustler]

Could you share a small example of your data so that we can establish what's going on?

Matty

Below is my sample data (see formulas in the "TB" sheet):

https://app.box.com/s/0n9qvwdivk1p6yk8d8020mqi6d1gyh90

You will need to update the beginning part of the formula using the one I posted above.

 

[Matty]

Below is my sample data:

https://app.box.com/s/0n9qvwdivk1p6yk8d8020mqi6d1gyh90

OK, so what are you trying to do? What range is the formula referencing?

Matty

 

[legalhustler]

OK, so what are you trying to do? What range is the formula referencing?

Matty

I'm trying to extract unique GL's and their total dollar amount in columns D, E, respectively from the text strings in column A in the "TB" sheet. However, the GL's from the text string in column A first needs to match with the GL's "Rules" sheet (D3:D6) then return it's corresponding SGL (E3:E6). If you look at the "Expected Results" sheet, it'll make more sense. I am trying to avoid helper columns.

The portion of the formula I posted above, is supposed to give a unique count of 4 (like the one's on "Expected Results" sheet). The current formula that you see in the link, is only matching the GL's from the text string in column A of the "TB" sheet to what's in the "Rules" sheet (D3:D6) without returning it's corresponding SGL. As you see for example, GL 102400 rolls up to SGL 101000.

This is why when you look at the "Expected Results" sheet you see SGL 101000 should have a dollar value of (292,322.03), because A6 of the "TB" sheet, while keeping in mind that 102400 rolls up to SGL 10100 based on the "Rules" sheet.

I used defined names for the ranges. You should be able to click and select the drop down on the left of the menu bar to see the referenced ranges.

 

[Matty]

It'd be possible to do this via formula, albeit a pretty complicated one.

I know you said you wanted to avoid helper columns, but how about parsing the text first which would then allow for much simpler formulas to be used?

Just a thought...

Matty

 

[legalhustler]

It'd be possible to do this via formula, albeit a pretty complicated one.

I know you said you wanted to avoid helper columns, but how about parsing the text first which would then allow for much simpler formulas to be used?

Just a thought...

Matty

Yeah, I have been using the F9 key to evaluate each part of the formula but just can't seem to get the unique count of text GL's in column A of the "TB" sheet to match with GL's first then get the corresponding SGL from the "Rules" sheet.