+ Reply to Thread
Results 1 to 21 of 21

Is there a function that would perform the inverse of countif?

  1. #1
    Registered User
    Join Date
    05-13-2013
    Location
    Franklin, Tn
    MS-Off Ver
    Excel 2010
    Posts
    16

    Is there a function that would perform the inverse of countif?

    So I understand that countif would give me a total # of a value in a set range. But what I need to do is take the inputed total # and populate the range with the value. Does anyone know a way to do that?

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Is there a function that would perform the inverse of countif?

    Hi sixsteps,

    As per me formula would not be helpful here as housing cells need to be ready with formula which is not feasible... suggest you to go for macros (vba).


    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Is there a function that would perform the inverse of countif?

    A great question!

    Say the original problem was to count the number of "Apples" in A1 thru A100 and our formula was:

    =COUNTIF(A1:A100,"Apples")

    to reverse this in B1 enter 17 and in B2 enter Apples

    Then in A1 enter:
    =$B$2

    In A2 enter:
    =IF(COUNTIF($A$1:A1,$B$2)>=$B$1,"",$B$2)

    Copy A2 down thru A100
    Gary's Student

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Is there a function that would perform the inverse of countif?

    Perhaps the FITNUOC function?

  5. #5
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Is there a function that would perform the inverse of countif?



    nice one Pepe



    Regards,
    DILIPandey
    <click on below * if this helps>

  6. #6
    Registered User
    Join Date
    05-13-2013
    Location
    Franklin, Tn
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Is there a function that would perform the inverse of countif?

    @ Jakobshavn

    I see what you're saying and I think that makes sense for what I'm needing to do. Can you take a look at this and help me further? I've imported the sheet on google drive, here is the link:

    https://docs.google.com/spreadsheet/...oTlNJd1E#gid=0

    What I'm needing to do is populate I4:33 with the data from table array D:35,E:39.

    Does that make sense?

  7. #7
    Registered User
    Join Date
    05-13-2013
    Location
    Franklin, Tn
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Is there a function that would perform the inverse of countif?

    I'm not familiar with this function. Can you explain for me?

  8. #8
    Registered User
    Join Date
    05-13-2013
    Location
    Franklin, Tn
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Is there a function that would perform the inverse of countif?

    I see what you're saying and I think that makes sense for what I'm needing to do. Can you take a look at this and help me further? I've imported the sheet on google drive, here is the link:

    https://docs.google.com/spreadsheet/...oTlNJd1E#gid=0

    What I'm needing to do is populate I4:33 with the data from table array D:35,E:39.

    Does that make sense?

  9. #9
    Registered User
    Join Date
    05-13-2013
    Location
    Franklin, Tn
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Is there a function that would perform the inverse of countif?

    How would you do a Macro DILIPandey?

  10. #10
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Is there a function that would perform the inverse of countif?

    May be I would follow the logic as - macro will ask you a range / column / rows etc and will input your desired value X number of times in that area.
    - where x is your desired count.
    For example, you'll say range = A1:B10, desired value = "abc" , X = 7, now macro will populate "abc" 7 times in the range a1:b10.

    Regards,
    DILIPandey
    <click on below * if this helps>

  11. #11
    Registered User
    Join Date
    05-13-2013
    Location
    Franklin, Tn
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Is there a function that would perform the inverse of countif?

    Thanks DILIPandy, I just sent you an email btw!

  12. #12
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Is there a function that would perform the inverse of countif?

    Please post under this thread only


    Regards,
    DILIPandey
    <click on below * if this helps>

  13. #13
    Registered User
    Join Date
    05-13-2013
    Location
    Franklin, Tn
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Is there a function that would perform the inverse of countif?

    Ok so I've been playing around with your suggestions. I have no experience with macros, so I've began to research that some.

    With Jakobshavn formula above, this is great for one value, but I have multiple values with multiple quantities. So my table looks something like this

    Apples 5
    Grapes 5
    Bananas 10
    Pears 10

    I need for those values to populate a column, in rows 1-30.

  14. #14
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Is there a function that would perform the inverse of countif?

    If you post your workbook, we can make specific suggestions.

  15. #15
    Registered User
    Join Date
    05-13-2013
    Location
    Franklin, Tn
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Is there a function that would perform the inverse of countif?

    Apologies for my ignorance, how do I do that?

  16. #16
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Is there a function that would perform the inverse of countif?

    When you reply, click Go Advanced, then click the PaperClip, then Select the file, then Upload the file, then touch Done, then touch Submit.
    Last edited by Jakobshavn; 05-14-2013 at 11:04 AM.

  17. #17
    Registered User
    Join Date
    05-13-2013
    Location
    Franklin, Tn
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Is there a function that would perform the inverse of countif?

    Great thanks so much! sheet is now attached. I'm needing the data in the small table D35:E39 to populate the column I4:I33.
    Attached Files Attached Files

  18. #18
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Is there a function that would perform the inverse of countif?

    You can do it more easily with a helper column. Put this formula in F36

    =SUM(E$35:E35)

    and copy down to F39

    then this formula in I4 copied down

    =LOOKUP(H4-1,F$36:F$39,D$36:D$39)

    ....or without a helper column use this version in I4

    =LOOKUP(H4-1,SUBTOTAL(9,OFFSET(E$35,0,0,ROW(E$36:E$39)-ROW(E$36)+1)),D$36:D$39)
    Last edited by daddylonglegs; 05-14-2013 at 11:20 AM.
    Audere est facere

  19. #19
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Is there a function that would perform the inverse of countif?

    Now that you have posted the workbook, it is obvious that my simple approach will not meet your needs. My approach will fill DTX, but it does not "know" when to switchover to RTC. Because the number of rows of data associated with DTX will change as cell E36 changes, a macro may be the best approach.

    Perhaps one of the other Responders can help us.

  20. #20
    Registered User
    Join Date
    05-13-2013
    Location
    Franklin, Tn
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Is there a function that would perform the inverse of countif?

    Many thanks this is beautiful! I added and tweaked just a bit to return a NS (no service) for the situations when there are not 30 days of service.

    Thank you guys! All of you!

  21. #21
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Is there a function that would perform the inverse of countif?

    In case you need it, see the attached:
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1