# Frequency Functions has N/A in Data Array Arguement

#### legalhustler

##### Well-known Member
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)

Last edited:

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Could you share a small example of your data so that we can establish what's going on?

Matty

Last edited:
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.

Last edited:
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

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.

Replies
8
Views
311
Replies
2
Views
174
Replies
14
Views
334
Replies
4
Views
689
Replies
2
Views
207

1,207,390
Messages
6,078,206
Members
446,321
Latest member
thecachingyeti

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back