Formula to sum filtered data based on criteria but excluding any errors i.e #N/A

[Howardc1001]

I have set up a formula in Col B to sum the values in Col B on sheet "Imported data" where the items in Col K to be summed is the same as the criteria in Col A , Error items i,e #N/A is to be excluded


Your assistance in amending my formula is most appreciated

[AliGW]

You have forgotten to include your expected answers, manually mocked up. Also there is no data in column K.

Please add annotaions explaining your requirements and expected results to your workbook. Sharing non-working formulae does not help very much.

[Glenn Kennedy]

Maybe:

=SUMPRODUCT(--IFERROR(('Imported Data'!$E$2:$E$20=$A1),0),SUBTOTAL(109,OFFSET('Imported Data'!B$2:B$20,ROW('Imported Data'!B$2:B$20)-ROW('Imported Data'!B$2),0,1,1)))

[Fluff13]

How about add a new column & in F2 put

Please Login or Register  to view this content.

and drag down, then you can use

Please Login or Register  to view this content.

far simpler & more efficient.

[Howardc1001]

My Apologies Ali. I was unfortunately in a rush as I had a meeting

[Howardc1001]

Thanks Fluff & Glenn. Both your solutions worked

[Fluff13]

Glad to help & thanks for the feedback.

[AliGW]

Administrative Note:

Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here.

Please see Forum Rule #5 about thread duplication.

I am closing this thread, but you may continue here in the original thread: https://www.excelforum.com/excel-for...-criteria.html