Array formula not recognizing alphanumeric data naming system - how to fix?

kelle

Board Regular
Joined
Apr 1, 2015
Messages
93
I'm having some trouble with a spreadsheet that I previously asked for help with here. Our business is rehauling its internal records management systems and I've been working on a spreadsheet to help track everything. Basically, right now I have a spreadsheet with a total of 281 individual records, some of which are in the same box. As different types of records have different retention schedules, I have a formula in column N that spits out the maximum retention date depending on if the box number is unique or not (if not, it calculates the maximum retention date).

What I needed help with was determining the numbers in O1-3. O1 is the active records count, so all records that have not been destroyed, minus O2, which is a count of all cells in D with any value entered into them. However, those represent individual records, and I need to know how many total boxes we still have on site, which needs to include a calculation for any boxes that have multiple records in it, like box 1817 below.

I got help here for the array formula in O3, which looks like this:
=SUM(IF(FREQUENCY(IF(NOT(ISNUMBER(O5:O5000)), D5:D5000, ""), IF(NOT(ISNUMBER(O5:O5000)), D5:D5000, ""))>0, 1, 0))-1

and it worked fine until I went through another huge file room of records and realized that the box naming convention had changed to include leading alphabetical characters (eg. FA-2007-17). The formula will only count any item that is a number and ignores any of the boxes with the alphanumeric naming convention.

Any help on how to change the formula to account for alphanumeric and numeric box titles?

vTygQNX.png


Thanks so much for any help you can offer me! I never realized how powerful Excel was until I started wading through these forums.
 
This worked perfectly! You're a trooper--thanks so much for sticking with it! I appreciate your help and the links with the information--hopefully I'll be able to figure this out myself next time!
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Yay, result!

Sorry it took so long. Worksheet formulas are not what I do best - but I do like a challenge. :)

Anyway, I am glad it is working for you.

Best regards,
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top