Subtotal with sumproduct and countif

[canuck99]

I need to use the SUBTOTAL function within a formula that uses SUMPRODUCT AND COUNTIF. The formula I'm using does a count of records in a list, eliminating duplicates. Now I need to use the SUBTOTAL function along with it to calculate the count of records, eliminating duplicates, when I filter on one of the fields. The formula I'm currently using looks like this:

=SUMPRODUCT(1/COUNTIF(i6:i13,i6Li13))

Thanks a lot

 

[ExcelElliott]

I've run into this problem before and used this website, hope it helps:

https://www.extendoffice.com/docume...ount-unique-values-in-filtered-column.html#a1

 

[canuck99]

I've run into this problem before and used this website, hope it helps:

https://www.extendoffice.com/docume...ount-unique-values-in-filtered-column.html#a1

Thanks for the tip, however it didn't work. The formula returned #VALUE . Any suggestions? Thanks.

 

[ExcelElliott]

Without looking at your formula, my first guess is that you ranges are not equal, i.e. if the data you want to uniquely count is in column A, rows 2 to 100, make sure all your cell references say A2:A100.

Also, it is an array formula so make sure to press Ctrl + Shift + Enter

 

[DanteAmor]

Try this:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(I6,ROW(I6:I13)-ROW(I6),0)),1/COUNTIF(I6:I13,I6:I13))

 

[canuck99]

Try this:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(I6,ROW(I6:I13)-ROW(I6),0)),1/COUNTIF(I6:I13,I6:I13))

This formula works perfectly!! Thank You

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.