discount #NA in sumproduct subtotal offset

[Ulli8]

Hi,

I want to count all dates in column B that are within one year back or date in the future. The below formula works and ignores blank cells, which is exactly what I want, however, the dates are from a vlookup formula, which returns #n/a when it can't find a matching value on the source sheet and if there is even one #n/a in the column that is all that is returned. Is there a way to only exclude #n/a?
In the table below I control counted manually in the right column dates further back than one year (3), dates within the last year (4) and dates in the future (5). I want the formula to return 9 instead of #n/a.


=SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B23,ROW(B6:B23)-ROW(B6),0,1)),--(B6:B23>TODAY()-365))

[TABLE="width: 218"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28/12/14[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]24/12/14[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]15/12/14[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]15/11/14[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]14/10/14[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]05/06/14[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/14[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]24/03/14[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]15/10/13[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/13[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]26/03/13[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]15/10/12[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]


Many thanks,

Ulli

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(SUBTOTAL(3,OFFSET(B6:B23,ROW(B6:B23)-ROW(B6),0,1)),
  IF(ISNUMBER(B6:B23),IF(B6:B23>TODAY()-365,1))))

 

[pgc01]

Hi

Another option, better IMO, is to not to have the error values. You just have to test for the error in your VLookup() formula, for ex. with IfError().

 

[Ulli8]

Thank you, Aladin,

for some reason this returns a 0 but should count 9 as below? I want it to count the green and purple dates.
And I need the #NAs because they are missed on the list and need to see them in detail so they can be followed up on separately, but I don't want them counted because they don't fulfil conditions. I hope this makes sense.

[TABLE="width: 282"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]row nos below[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]28/12/14[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]24/12/14[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]15/12/14[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]#N/A[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]#N/A[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]15/11/14[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]14/10/14[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]05/06/14[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]16/04/14[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]#N/A[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]24/03/14[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]15/10/13[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]16/04/13[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]#N/A[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]26/03/13[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]15/10/12[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


Many thanks, and best regards,

Ulli

 

[Aladin Akyurek]

Thank you, Aladin,

for some reason this returns a 0 but should count 9 as below?

...

Many thanks, and best regards,

Ulli

Did apply control+shift+enter for confirming the formula as requested?

See the workbook that implements the conditional SUBTOTAL formula:
https://dl.dropboxusercontent.com/u/65698317/Ulli8 conditional subtotal.xlsx

 

[Ulli8]

Oh dear, Aladin,

I hold my head in shame, you are right, now it works

Thank you so very much!!!

Have a nice evening

 

[Aladin Akyurek]

Oh dear, Aladin,

I hold my head in shame, you are right, now it works

Thank you so very much!!!

Have a nice evening

You are welcome. Have a nice evening too.