Countif

[Deano]

Hi

Assuming I have a file with many rows or product codes and want to count the number of times each product code is duplicated and insert this value into a column in the data, I would use the COUNIF formula. Now if I want this to be modified to only count the visible rows when I apply filtering, can anyone tell me how to modify my formula?

Regards

 

[Fazza]

Maybe the SUBTOTAL function instead?

 

[Aladin Akyurek]

Hi

Assuming I have a file with many rows or product codes and want to count the number of times each product code is duplicated and insert this value into a column in the data, I would use the COUNIF formula. Now if I want this to be modified to only count the visible rows when I apply filtering, can anyone tell me how to modify my formula?

Regards

=SUMPRODUCT(SUBTOTAL(3,OFFSET(F5:F600,ROW(F5:F600)-ROW(F5),,1)),--(F5:F600=ProdCode))

Adjust the range to suit.

 

[Deano]

Hi Fazza

I thought of this, but using the subtotal within the rows of data seems a bit tough? I normally use subtotal(3,etc) outside the "database" and not inside it. What's your suggestion??

 

[Fazza]

Hi Deano,

I didn't initially understand about wanting the result within the data - I thought/assumed it was at the bottom. So, please disregard my suggestion.

FWIW, it does sound though that inputs and reporting are being mixed up together. If there is significant data or the reporting becomes too difficult, you might consider having a dedicated data input section and a separate output/report section.

Beer o'clock for me. Regards, Fazza