how to combine the subtotal() and countif() functions in excel filter

Topic

New Reply

Hello,

I want to use a countif function, combined with a cell(“address”) function, to count the number of times a letter appears within a column immediately it is inputted onto the column, meanwhile the worksheet would be a filtered sheet. So, I do not want the countif function to refer to columns that are not in the present recordset/view.

Please, i need an urgent response.

Thank you.

Bola

Reply | Quote

Replies

Perhaps you can adapt these:
=SUMPRODUCT(–($C$2:$C$20=”A”),SUBTOTAL(3,OFFSET(C2,ROW($C$2:$C$20)-ROW(C2),0)))
will count the filtered cells in the range C2:C20 if they are equal to A

=SUMPRODUCT(–(ISNUMBER(SEARCH(“A”,$C$2:$C$20))),SUBTOTAL(3,OFFSET($C$2,ROW($C$2:$C$20)-ROW($C$2),0)))
will count the filtered cells in the range C2:C20 that contain at least 1 “A”

Steve

Reply | Quote

Hi Steve,

Thank you for your response. However, i have not been able to put it to use just yet. If i am able to get the formular to return the count, i am convinced that it would work. I also want to know if the ‘–‘ here “=SUMPRODUCT(–” is permissible by excel. I have never used such before.

I’d appreciate your response. However, please also note that my formular uses this line to identify the letter selected in the active cell, “(indirect(cell(“address”)) “.

Thank you.

Reply | Quote

Hi Steve,

I am actually now writing back to thank you, with a BIG HUG. Your code worked for me. I used the second line, which did it. It was unbelievably fantastic. I wouldnt have thought about that sumproduct, subtotal, isnumber, search, offset combo. It was iBADt.

below is a clip showing how i used it.

30481-Stevies-code-worked_incorporated

=IFERROR(IF((SUMPRODUCT(–(ISNUMBER(SEARCH(INDIRECT(CELL(“address”)),$X$4:$X$2400))),SUBTOTAL(3,OFFSET($X$4,ROW($X$4:$X$2400)-ROW($X$4),0))))>=ROUND(VLOOKUP(INDIRECT(CELL(“address”)),Distribution!H8:I12,2,FALSE),0),”Slot for Rank “&INDIRECT(CELL(“address”))&” is now complete”,”Slot for Rank “&INDIRECT(CELL(“Address”))&” now open”),”Ready, Select Ranks”)

I am sooo grateful.

Reply | Quote

The — is “permissable” to excel, it creates a “double negative” which is a positive. The ($C$2:$C$20=”A”) creates a True/False array, the first negative turns the trues to -1 and the falses to 0, the second negative turns the -1s to 1s for the multiplication to work.

I saw you mention the cell address, but couldn’t figure out how it related to the question so just put an example explicitly listing a letter and used explicit ranges. It would also work with indirect references. It is an example formula, you must adapt it to your exact situation since you don’t detail anything.

Perhaps you should attach an example detailing more of what you need if you can’t adapt what I listed.

Steve

Reply | Quote

Hi Steve, I hope you have seen the rejoinder that I sent after the first post that I sent? I figured out why it didnt work initially; I walked through the formular and found that (either as a result of it being copied from an HTML source, or typographic error) a part of the initial code that you sent “….OFFSET(, was written as ‘OFFS ET(‘, with a space. When i noticed, i went back to delete the space character, and it worked.

The INDIRECT(CELL(“address”)) only returns the content of the active cell for the formular to use as input. So, for instance if there is a dropdown list for validation on a cell and I select, say, alphabeth “A” from the list into a cell, that cell becomes the active cell, whereever it may be on the worksheet…the letter “A” that has been selected is also returned for another function to use, as its own input. I hope you understand my explanation.

You have really helped me, though. Thank you

Reply | Quote