+ Reply to Thread
Results 1 to 8 of 8

Combining Functions Countif & Frequency

  1. #1
    Registered User
    Join Date
    10-09-2010
    Location
    kuwait
    MS-Off Ver
    Excel 2007
    Posts
    3

    Question Combining Functions Countif & Frequency

    I am trying to combine two functions countifs & frequency to no avail. The individual formulas are listed below. To explain the purpose here is a simple example. If there is a diner, I want to know how many people order breakfast in a month on odd days within a price range. I then want to know how many of those people are unique users and not repeat accounts. Any alternative solutions more than welcome. %Right now I am limited to finding all accounts or all counts of instances meeting the criterias.

    =COUNTIFS(Sheet2!$E:$E,Sheet1!$B$1,Sheet2!$D:$D,Sheet1!$D$1,Sheet2!$G:$G,">="&Sheet1!$B24,Sheet2!$G:$G,"<"&$C24)

    =SUM(IF(FREQUENCY(Sheet2!b:b,Sheet2!b:b!)>0,1))
    Last edited by vagabond; 10-10-2010 at 01:20 PM. Reason: Solution Voluteered

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Combining Functions Countif & Frequency

    Hi vagabond,

    I seem to have a Pivot Table soap box that I stand on and think Pivot Tables can do it all.

    Please post a sample (or the whole) spreadsheet with some data so I can try to easily solve your problem.

    Pivot tables have Filters, Groups and Sorts for data. The data can also be Summed, Mined, Mixed or Counted. Your problem seems like a UserCount of 1 (Unique User). You can sort by Date so doing a monthly grouping is no problem. You can group or filter by price paid. If you have a Time on your dates you can also Filter or Group by Hour of Day the Order happened thus implying breakfast.

    To attach a file, click on "Go Advanced" and then the Paper-Clip icon above the message. This will open a dialog box to attach a sample file.

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

    Re: Combining Functions Countif & Frequency

    How much data do you have? There are possible formula solutions but they possibly won't work very well with large amounts of data.......
    Audere est facere

  4. #4
    Registered User
    Join Date
    10-09-2010
    Location
    kuwait
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Combining Functions Countif & Frequency

    Thank you. Yes, I preach pivots to no avail. Thank you for your response. If you are taking the time to help, let me be specific. The pivot table lets me play with the data and analyze it as need be. What is does not allow me to do is select ie. a symbold, place it in a range and give me the relevant data. The second spreadsheet is me trying to do that with similar data.

    PLEASE PLEASE Please do not say 'easily solved'. Too deflating Thanks for your effort and time.
    Attached Files Attached Files

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

    Re: Combining Functions Countif & Frequency

    easily solved .....

    You could use this formula in H5

    =SUM(IF(FREQUENCY(IF(Sheet2!G$3:G$1000>=B5,IF(Sheet2!G$3:G$1000<=C5,IF( Sheet2!D$3:D$1000=D$1,IF(Sheet2!E$3:E$1000=B$1,IF(Sheet2!B$3:B$1000<>"",MATCH(Sheet2!B$3:B$1000, Sheet2!B$3:B$1000,0)))))),ROW(Sheet2!B$3:B$1000)-ROW(Sheet2!B$3)+1),1))

    confirmed with CTRL+SHIFT+ENTER and copied down

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Combining Functions Countif & Frequency

    Hi,

    Find attached your Pivot Table with some simple code behind a ReGroupx10 button. I decided if you first picked a symbol and then found the Max price and Min price and made the step of 10. you could group the pivot table by these ranges. Then if you counted the accounts that sold/bought/ etc in this range it was what you may want.

    The attached is a little crude. You pick a symbol to show and then the ReGroup button. You need to pick a symbol that has enough data to have some variance to see it work.

    This may be closer to what you want. It wasn't "too easy" but because you can code, may get you to where you want to go.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-09-2010
    Location
    kuwait
    MS-Off Ver
    Excel 2007
    Posts
    3

    Smile Re: Combining Functions Countif & Frequency

    Thanks a lot. I am getting the dreaded 'VALUE' but I see what you are doing. I am still a newbie so I am tracing what you did with the MATCH and ROW but thank you very much. Disturbingly fast for you, frustrating for me but ultimately fun.

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

    Re: Combining Functions Countif & Frequency

    You probably haven't confirmed with CTRL+SHIFT+ENTER

    To do that, select cell with formula, press F2 then hold CTRL and SHIFT keys and press ENTER. Curly braces like { and } should appear around the formula in the formula bar

    See attached in sheet 1
    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