+ Reply to Thread
Results 1 to 12 of 12

FREQUENCY function with multiple IFS

  1. #1
    Registered User
    Join Date
    11-14-2018
    Location
    Belgium
    MS-Off Ver
    Microsoft office 365 Proplus
    Posts
    7

    Question FREQUENCY function with multiple IFS

    Hello, I'm new to this forum and just started a job in excelthings.

    I have a whole table of multiple (3565) different orders coming from a "couple" (110) of producers.
    I would like to get the frequency of these orders per producer but depending of the place where this order was set AND who transports the order, which are in different columns.
    => How many times did transporter A move an order from producer B (IF 1) too place C (IF 2)
    I found a formula to implement 1 IF in the frequency formula but not multiple. This seems more difficult because of the way the IFS formula is set up. (test1, result1, test2, result2,...) since I only want 1 result I don't know how to proceed.

    i hope my question is somewhat logical.

    thanks in advance

  2. #2
    Banned User!
    Join Date
    10-12-2018
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    358

    Re: FREQUENCY function with multiple IFS

    Hi,

    Maybe if you can attach a sample workbook showing your desired result someone can derive a formula.

    Thanks

  3. #3
    Registered User
    Join Date
    11-14-2018
    Location
    Belgium
    MS-Off Ver
    Microsoft office 365 Proplus
    Posts
    7

    Re: FREQUENCY function with multiple IFS

    Hi,

    Thank you for the reply, I think a sample size is a good idea since I don't even get my explanation lol

    So the idea is to have the number of "producers" a transporter worked with in a departement.

    For example: Dandilan worked with 3 producers in the departement 11
    Logistics worked with 2 producers in the departement 11
    Dandilan worked with 1 producers in the departement 13
    I would like a formula that gives me the number 3; 2; 1
    sample.PNG

  4. #4
    Banned User!
    Join Date
    10-12-2018
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    358

    Re: FREQUENCY function with multiple IFS

    Hi again,

    I put your sample data in a workbook (preferred method for formula help). Using a countifs might be a solution:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by xjohnson; 11-14-2018 at 11:43 AM.

  5. #5
    Registered User
    Join Date
    11-14-2018
    Location
    Belgium
    MS-Off Ver
    Microsoft office 365 Proplus
    Posts
    7

    Re: FREQUENCY function with multiple IFS

    Hi, you are one dedicated bro :D

    using your function you do not include the different producers which is the main part of the problem.
    The number 3 for dandilan is not just because it transported 3 times in the 11th departement but because it was with 3 different producers: Jules de Trooz, Caramba and Stylo
    i don't think you can implement this in a countif as well, would be dope tho.

    Thank you so much for the follow ups tho

  6. #6
    Banned User!
    Join Date
    10-12-2018
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    358

    Re: FREQUENCY function with multiple IFS

    Okay I attached a revision showing what I think you want. If you put Department in F2 and Transporter in G2 the below array will fill all occurrences with the Names of Producers that match that criteria. Cell H2 will show the number of occurrences.

  7. #7
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    921

    Re: FREQUENCY function with multiple IFS

    A
    B
    C
    D
    E
    F
    G
    H
    1
    Date
    Department
    Transporter
    Name of Producer
    Department
    Transporter
    count unique Producer
    2
    1-Jul
    11
    Dandilan Jules de Trooz
    11
    Dandilan
    3
    3
    2-Jul
    11
    Logistics Jules de Trooz
    4
    3-Jul
    12
    Dandilan Jules de Trooz Name of Producer
    5
    4-Jul
    12
    Mission Jules de Trooz Jules de Trooz
    6
    5-Jul
    11
    Cerlato Samalela caramba
    7
    6-Jul
    12
    Cerlato Samalela Stylo
    8
    7-Jul
    13
    Logistics Samalela
    9
    8-Jul
    13
    Dandilan Samalela
    10
    9-Jul
    13
    Cerlato Samalela
    11
    10-Jul
    14
    Teprenpa jumbo
    12
    11-Jul
    14
    Cerlato jumbo
    13
    12-Jul
    14
    Logistics jumbo
    14
    13-Jul
    11
    Dandilan caramba
    15
    14-Jul
    11
    Teprenpa caramba
    16
    15-Jul
    11
    Mission caramba
    17
    16-Jul
    11
    Logistics Stylo
    18
    17-Jul
    11
    Dandilan Stylo
    19
    18-Jul
    12
    Logistics Stylo


    H2=SUM(IF(FREQUENCY(IF($B$2:$B$19=$F$2,IF($C$2:$C$19=$G$2,MATCH($D$2:$D$19,$D$2:$D$19,0))),ROW($D$2:$D$19)-ROW($D$2)+1),1)) control+shift+enter

    H5=IFERROR(INDEX($D$3:$D$19,SMALL(IF(FREQUENCY(IF($B$2:$B$19=$F$2,IF($C$2:$C$19=$G$2,MATCH($D$2:$D$19,$D$2:$D$19,0))),ROW($D$2:$D$19)-ROW($D$2)+1),ROW($D$2:$D$19)-ROW($D$2)+1),ROWS($D$2:D2))),"")

    control+shift+enter copy down

  8. #8
    Registered User
    Join Date
    11-14-2018
    Location
    Belgium
    MS-Off Ver
    Microsoft office 365 Proplus
    Posts
    7

    Re: FREQUENCY function with multiple IFS

    Hello guys

    Thanks for the replies.
    The formula you suggested xjohnson is a bit too complicated to implement in a table with 3535 rows. Also is the whole idea a bit too much for my knowledge. Thank you tho

    The formula by Caracalla does work and I was able to implement it but only on 1 column. I don't get everything you put into it and I think I have to change something to make it work in other columns as well.
    Concreet: I have arranged the different transporters in different columns and in the rows the different departments. I changed the cells to refer to the right transporter and departement but it doesn't work.
    Is there something in the lines "ROW" I have to change as well? It is this part I don't get in the formula.

    I hope you can still follow my drift.

    THankss

  9. #9
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    921

    Re: FREQUENCY function with multiple IFS

    Hi

    Try to provide a small data sample (~20 rows) along with expected result.

    Not image please

  10. #10
    Banned User!
    Join Date
    10-12-2018
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    358

    Re: FREQUENCY function with multiple IFS

    Sean,

    It is not that complicated all you have to do is to copy the formula in F5 in my example then press ctrl+shift+enter to make it an array. This will put the curly brackets at the beginning and end of the formula. Then drag it across, then drag it down to fill the range of the possible number of outcomes you may have. This could be any amount so make it 25 to be sure.

    Learn from the example as far as the cell numbers and where they are you can do it!

  11. #11
    Registered User
    Join Date
    11-14-2018
    Location
    Belgium
    MS-Off Ver
    Microsoft office 365 Proplus
    Posts
    7

    Re: FREQUENCY function with multiple IFS

    IT FINALLY WORKED!!

    Thanks all for the help it was a hassle and wouldn't have done it without both of you.
    In the end the problem with the columns was that my department numbers weren't stored as numbers but as text. Stupid but not my fault lol

    If you have a bit more time I would like an explanation for the last part of the code: ROW($D$2:$D$19)-ROW($D$2)+1),1)) which I don't get.

    But still thanks

  12. #12
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    921

    Re: FREQUENCY function with multiple IFS

    Hi



    Ctrl + Shift + Enter: Excel Array Formulas 18: Unique Count Formulas: FREQUENCY or COUNTIF function?

    https://www.youtube.com/watch?v=ELBoMyZxBM0

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. adding a Frequency function to a CountIFS function
    By Plucky_ in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-12-2017, 08:13 PM
  2. [SOLVED] Frequency Function
    By TPAge1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-07-2014, 02:31 PM
  3. Replies: 1
    Last Post: 07-16-2013, 01:54 AM
  4. Frequency Function with Multiple Criteria
    By LilSisKin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-08-2013, 03:00 PM
  5. Excel 2007 : help with a FREQUENCY function
    By jenya_utd in forum Excel General
    Replies: 5
    Last Post: 02-06-2012, 07:52 AM
  6. Using Frequency Function
    By mdsaleem.29 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-25-2011, 10:44 AM
  7. [SOLVED] Frequency function
    By Pritesh in forum Excel General
    Replies: 2
    Last Post: 04-18-2005, 05:06 PM

Tags for this Thread

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