View Poll Results: Have you ever encoundered or solved this type of problem

Voters
1. You may not vote on this poll
  • Yes

    0 0%
  • No

    1 100.00%
Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Mar 2012
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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,OFFS ET($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

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    bola (2012-03-29)

  4. #3
    New Lounger
    Join Date
    Mar 2012
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  5. #4
    New Lounger
    Join Date
    Mar 2012
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

    Stevie's code worked_incorporated.jpg

    =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("addr ess")),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.

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  7. #6
    New Lounger
    Join Date
    Mar 2012
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •