# FREQUENCY function with multiple IFS

1. ## 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.

2. ## 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. ## 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. ## 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:
`Please Login or Register  to view this content.`

5. ## 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. ## 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. ## 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. ## 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. ## Re: FREQUENCY function with multiple IFS

Hi

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

10. ## 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. ## 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. ## Re: FREQUENCY function with multiple IFS

Hi

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

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