COUNTIF FUNCTION FOR FILTERABLE DATA

Summary:-

While using COUNTIF function of Excel, the count if function will not work for filterable data as the values will not change as per the data filtered. As on today no other single formula is available for using count if function in filterable data. 

To overcome this difficulties, we can use a complex function formula as mentioned below:-

copy paste the below formula and input he field highlighted. 

SUMPRODUCT(SUBTOTAL(3,OFFSET(K4,ROW(K4:K38)-ROW(K4),0)),(K4:K38="P4")+0)

Highlighted text are the changeable data we have to input as per our requirements.

Unlock Excel spreadsheet with VBA code (for Excel 2010 and lower)

Unlock Excel spreadsheet with VBA code (for Excel 2010 and lower)

If you are using Excel 2010 or older version, you can unlock a password-protected sheet with the following macro.

1. Press Alt + F11 or Right click the sheet and view code to open the Visual Basic Editor.
2. Right-click the workbook name on the left pane (Project-VBAProject pane) and select Insert >Module from the context menu.
3. In the window that appears, paste in the following code:
   ?

   Sub PasswordBreaker()     'Breaks worksheet password protection.       Dim i As Integer, j As Integer, k As Integer     Dim l As Integer, m As Integer, n As Integer     Dim i1 As Integer, i2 As Integer, i3 As Integer     Dim i4 As Integer, i5 As Integer, i6 As Integer       On Error Resume Next       For i = 65 To 66: For j = 65 To 66: For k = 65 To 66     For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66     For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66     For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126           ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _             Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _             Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)           If ActiveSheet.ProtectContents = False Then             MsgBox "One usable password is " & Chr(i) & Chr(j) & _                 Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _                 Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)               Exit Sub           End If       Next: Next: Next: Next: Next: Next     Next: Next: Next: Next: Next: Next End Sub

4. Press F5 or click the Run button on the toolbar and wait a couple of minutes. The macro will report a cracked password, which is not the original one (always some combination of A's and B's), but nevertheless it works.

If you are not very comfortable with VBA, you can find the detailed guidance in the following tutorial: How to insert and run VBA code in Excel.

If you need to unprotect Excel sheet without password in the modern versions of Excel 2013 and Excel 2016, try one of free tools, for example Password Protection Remover add-in for Excel.

Copy the protected sheet contents to another worksheet

Another way (or rather a workaround) to unlock an Excel sheet that was protected with password is to copy its entire contents to a new sheet. Here's how:

1. Open the password-protected sheet.
2. With the cursor positioned in cell A1, press Shift + Ctrl + End to select all used cells in the worksheet.
3. Press Ctrl + C to copy the selected cells.
4. Create a new sheet in the current workbook or in a different workbook (to create a new Excel file, simply press Ctrl + N).
5. In the new blank worksheet, place the cursor in A1 and press Ctrl + V to paste the contents of the password-protected sheet.

Notes:

• This method works only if the Select locked cells and Select unlocked cells actions are allowed in the protected spreadsheet.
• If the password-protected worksheet has external links to other sheets or workbooks, you may need to re-create those links manually.
• You can use Paste Special to copy / paste the column widths so you won't need to adjust them in the new sheet manually.