SUBTOTAL VLookup #N/A How can Subtotal function ignore #N/A

[RxMiller]

I have three subtotals columns and a subtotal at the top of the Excel row 1 for each column. It reads the subtotal of each columns subtotal. The formulas all work if each of the sub-totals have a value.
The problem: if one of the many VLookup returns a #N/A, the Grand subtotal for that entire column of subtotals returns a #N/A

Is there a way to add a conditional option in the Excel Subtotal function?
I only want to Subtotal values that are numeric and just ignore the #N/A in a column.


I use Access 2003 and SQL Server 2005 to group data.
Then with Excel automation, I create a Workbook.
One Worksheet has all the Natural Gas Buy contracts, another the Natural Gas Sell contracts for a time period determined by the user interface.
From there, a very custom daily report with 3 columns of VLookup and subtotals managed by the VBA code, record counts, and so on.
I can change my VLookup code to an (If(vlookup finds a matching contract put price, else put zero) My preference is to leave the #N/A to indicate that there is not a matching contract, rather than put the value zero.

[DonkeyOte]

You should as you've inferred replace the Error values, however, you could replace with a Null or Text String rather than 0 if you prefer... given the result would be a Text string (Null seen as text) it won't cause an issue in your summation cells, eg:

=IF(ISNA(MATCH(criteria,left most range of vlookup,0)),"text",VLOOKUP(....))
(remove text (ie "") if you want a Null)

[RxMiller]

Thanks for the quick response, I was posting while you were responding...
This is not my first choice:
If VBA populates each cell formula with an If (ISERROR(...), the string "Not Found" is ignored by the Subtotal function
IF(iserror(vlookup tested, "Not Found", Vlookup numeric result)
=IF(ISERROR(VLOOKUP(70036,PositionDataBuy!$D$2:$U$505,17,FALSE)), "Not Found", VLOOKUP(70036,PositionDataBuy!$D$2:$U$505,17,FALSE))

This lets the end user know that the contract value was "Not Found" and allows the rest of the nested SubTotal functions work.

However, it seems like the Subtotal function should have an option to conditionally ignore an individual error.

The VBA is dynamically building the formula above knowing in advance all the values and the last row in Excel.
I will go change my code in two places and go with it for now.
If my end users have a better contract number to enter (i.e. 70036) they will have to substitute it in two places with this formula.

[DonkeyOte]

Edited: I composed below whilst you were editing your last response in response to my prior post

I'm not sure if the post is a rhetorical question, it sounds as though you know how to apply the formula dynamically with VBA - if not post back.

Re: Subtotal being able to ignore errors... why ?
It's not really logical IMO for unhandled errors to be ignored.

Depending on the function being performed via the SUBTOTAL be it counting or performing a subsequent numerical calculation errors would be ignored... eg a COUNT/COUNTA would proceed without issue whereas SUM/AVERAGE etc would all generate errors.

[RxMiller]

OK, I can live with that. If my "excel savvy users" whine too much, I will employ a custom function and go for the additional maintenance.

Here is a small code example in case other people find this useful (or not). The SubTotal function works like a champ now with "Not Found" in the few results. It totals the underlying subtotals (not shown here) that subtotal each group of values and no longer have a #N/A that cascade to the various levels of SubTotal function.

Please Login or Register  to view this content.