Subtotal combined with SumIF

[SimonaB]

Hello,

I have an issue with combining the formulas subtotal and sumif. I have read several solutions from here but none have worked...

I have this sumif for skipping N/A values: =SUMIF(K38:K2366;"<>#N/A")
and a subtotal for the same range, K38:K2366 would be required (sum).

Also, as an information, the range is a vlook-up, if that is relevant.

Thank you kindly for the responses.

S

 

[RoryA]

Welcome to the forum.

The simplest solution would be to add some error handling to the VLOOKUP formulas to return either "" or 0 instead of the error. Then you can use a straight SUBTOTAL formula. Or, if you have Excel 2010 or later, you can use AGGREGATE which you can tell to ignore errors.

 

[Special-K99]

A properly organised spreadsheet shouldn't be generating any #N/As.

Use

IFERROR(formula,"")

to remove them

 

[SimonaB]

Thank you very much for the solution. It hasn't even crossed my mind.

Have an excellent day!