Comments on: Excel array formula examples for beginners and advanced users

The tutorial focuses on Excel array formula examples and demonstrates how to use Excel functions that support calculations in arrays to count cells that meet several conditions, sum values in every Nth row, count any given character in a range, and more. Continue reading →

Comments page 2. Total comments: 66

  1. Edit:
    Minimum: =MIN(IF(($A$2:$A$9=$F$1) * ($B$2:$B$9=$F$2), $C$2:$C$9,""))
    I do understand it as for find Maximum but when using it to find Minimum I get lost, because the formula takes a value 0 in rows not matching ($F$1 AND $F$2), and then Min returned is 0. Where I'm wrong?

    TIA,

  2. I can read above:
    Using several functions in Excel array formulas
    In the screenshot above, the following Excel array formulas are used (don't forget to press Ctrl + Shift + Enter to enter them correctly):

    Maximum: =MAX(IF(($A$2:$A$9=$F$1) * ($B$2:$B$9=$F$2), $C$2:$C$9,""))

    Minimum: =MIN(IF(($A$2:$A$9=$F$1) * ($B$2:$B$9=$F$2), $C$2:$C$9,""))
    I do understand it as for find Maximum but when using it to find Minimum I get lost, because the formula take a value 0 in rows not matching ($F1$ AND $F$), and then Min returned is 0. Where I'm wrong? TIA,

  3. Hello,
    i have a sheet contains entry and exit time of an employee, i have to count the days on which employee came late.
    entry time is 09:00,
    if employee came during 09:00 to 10:30 than count how much time they late.

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  4. Hello,
    I am new to excel and my question is, suppose i am making use of array formula....can i do the things by normal formula methode?
    I know it will take more time, but can i do the complex things using normal formula method?

  5. Hi everybody...
    I have problem related, I think, to using IF function or IFS.
    I have a Pivot filter including 3 options, means I can select 1 or All or Multiple product categories. I am using SUMIFS to extract the data from Excel table, and filter using Pivot filter. all working fine until I select 2 product categories, I eliminate the problem when selecting (ALL), but could not do that when I select 2 product categories, the result was all zero values. here is my function:

    =SUMIFS(SalesData[revenue];SalesData[year];I$4;SalesData[region];$H5;SalesData[Category];IF($I$2="(All)";"*";$I$2))

    my attempt was nested IF:
    IF($I$2="(All)";"*";IF($I$2="(Multiple Items)";$I$2))
    but it dose not work, any help...

  6. I don't know if arrays can help me in this instance or what solution would in Excel, but here is the situation.

    I work at a dental office. Dental offices get paid only 1 of 2 ways, from a patient's insurance claims checks, or the patient pays out of pocket.

    So a patient's total account balance = the insurance balance + the patient balance.

    I am looking at the aging of accounts receivable, and there are 7 buckets in terms of time, 0 to 30 days old, 31 to 60, 61 to 90, 91 to 120, 121 to 150, 151 to 180 and 181+ days old or more as a catch all.

    I want to build a report that shows all 3 pieces for each patient:
    1. the total account balance by each of the 7 aging buckets
    2. the insurance balance portion only by each of the 7 aging buckets
    3. the patient balance portion only by each of the 7 aging buckets

    The dental software the office uses only gives perspective #1, so I have resorted to creating the other two perspectives in Excel.

    The part where I am stuck is when a patient has both types of balances, insurance and patient portion, but its a one to many relationship. For example, patient

    John Smith, could have an insurance portion balance in only 1 bucket, say 31 to 60 days old (for $128 as an example), but they have a patient portion balance (say $310) that is dispersed among 2 or more buckets, say the 121 to 150 day old bucket ($185) and the 181+ day old bucket ($125).

    What I want to do, what I am trying to do is have the insurance balance aging report read

    31 to 60, $128
    all other aging buckets on the insurance side for John Smith should show $0 or blank.

    The patient portion should read

    121 to 150, $185
    181+, $125
    all other aging buckets on the patient side for John Smith should show $0 or blank

    Two snags to this, the original output to the reporting software does something wonky when I dump it into Excel,

    1. it give the patient portion and insurance portion balances OUTSIDE the aging buckets, and disperses only the total account balances for each patient INSIDE or WITHIN the aging buckets.

    2. While an insurance balance portion can only be a positive number, a patient balance portion can either be positive or negative (negative meaning they have a credit on their account for future dental work, or we owe them money).

    Eric

  7. Hello,
    Can you please tell me how to write this below formula in excel $E$5-SUM($C$5:C6) how we create this in excel function?
    Either manualy or by hitting somewhere in table.

  8. as I enter values in three rows a1,a2,a3 those values should befollowed to next sheetand as I press enter a1,a2,a3 should become 0 .as I reenter the values in a1,a2,a3 those values should get copied to next column b1,b2,b3
    like this it should continue everytime

  9. I'm trying to use the =SUMPRODUCT(--ISNUMBER(SEARCH(LOWER(town),LOWER(T2))))>0 formula, that looks at an array of say 100 items. Is there a way rather than coming back true or false, that the formula can tell me the cell content it matched to. e.g. "Sydney" Thanks!

  10. IF IN THE PARTICULAR COLUMN THE CELL CONTAIN A,B AND C ALPHABET, HOW WE WILL SET THE FORMULA FOR COUNTING ONLY A & B TOGETHER.
    EXAMPLE:
    A
    A
    B
    C
    B
    COUNTING SHOULD BE 4.

    PLEASE ADVICE

    1. Suppose cell address of A is A1 & last B has address A5. Then the formula will be-
      =countif(A1:A5,"A")+countif(A1:A5,"B")

      I think this will serve the purpose.

  11. Hi there!!!

    Something, that I think is curious, is why, in your fourth example, you put "/ LEN(E1)" in your formula. According to me, you didn't need it: You can obtain the same result without it. But, any way, if you have time and you like, would you like to explain me? To me it would be something very illustrative.

    Take care, and thanks for your knowledge.

    1. Hi Sergio,

      Dividing by LEN(E1) is not needed when you count a single character like in the example you referred to. In situations when you want to count the occurrences of a specific substring in a range (i.e. a sequence of 2 or more characters), you need to divide by the substring length, otherwise each character in the substring will be counted individually (for example if you have entries like "cat-1", "cat-2", "dog-1, "dog-2", etc. and you want to get the total number of "cat" entries).

  12. Hi Team,
    i am a big fan of the nuggets on the website. i am trying to work on a problem which comprises of three parameters(columns) Date, Value,Outlier(0-outlier /1-safe value).I have to create a new column (say Output) which will have all normal value (Outlier = 1, in case there is a 0 it should have value corresponding to previous 1). I am not able to handle case when there are 3 or more sequential Outlier.
    Date Value Outlier Output
    1-Jan 355306 1 355306
    2-Jan 1283040 0 355306
    3-Jan 303244 1 303244
    4-Jan 668608 1 668608
    5-Jan 1249288 0 668608
    6-Jan 133452 0 668608
    7-Jan 1005512 1 1005512
    8-Jan 81904 0 1005512
    9-Jan 112200 0 1005512
    10-Jan 81780 0 1005512
    4-Jan 668608 1 668608

    Would appreciate your assistance with the above.

  13. this is really great & also helpful.

  14. hi there,

    I am trying to make a barcode scanner excel sheet. suppose in one coloum A, i have barcode numbers like 123456789, 987654321 etc and in colum B i have their names like product X, product Y etc. i want if i scan a barcode in column C and if it is present in the list A, it must be display its product name name from column B and if not present it must display any other message. can anyone help me out with a formula ?

    thanks

    1. If you want just to verify if Cx=Ax, then is really simple.

      =IF(C2=A2,B2,"")

      Copy formula in all column C cells (your range, not full column C).

  15. Hello,

    My formula is "locked" in somewhere, result is as expected, but when i tried to copy paste and a little tweak to adjusted between rows, result get empty. And then i realized my formula is "locked" and depend on the C5 input instead of criteria as intended to get the result.

    Below is my array formula, please review it.
    IFERROR(IF($C5:$C2219$H$5;"";IF(INDEX($D$5:$D$2219;SMALL(IF($C$5:$C$2219=$H$5;ROW($C$5:$C$2219)-ROW($C$4);"");J4))="AKUM";I5+INDEX($E$5:$E$2219;SMALL(IF($C$5:$C$2219=$H$5;ROW($C$5:$C$2219)-ROW($C$4);"");J4));MAX(I5;INDEX(($B$5:$B$2219)+($E$5:$E$2219);(SMALL(IF($C$5:$C$2219=$H$5;ROW($C$5:$C$2219)-ROW($C$4);"");J4))))));"")

    To get a better view, here i attach the image https://i.stack.imgur.com/5ptWT.png

    Hope someone can make a review or direct me to somewhere, because i seems can't get the google keyword to my problem.
    Initially i'm trying to make a ledger from daily journal, so u must be alert what im attempting to achieve.
    Would u review it.

  16. I am trying to integrate the every "N" example to sum a quarterly recurring revenue stream.
    Sales occur every month, we just need to sum the months that correspond to the current cells cohorts. Making it even more challenging, the sum range needs to be variable based upon an intro month and lifespan in months.
    Formula currently:

    (make sure we have an item to sum)
    =IF($B102"",
    (this id's the item as quarterly)IF(IFERROR(INDEX(Setup!$B$63:$L$77,SMALL(IF(Setup!$B$63:$B$77=$B102,ROW(Setup!$L$63:$L$77)-62),ROW($A$1)),11),"")="Quarterly",
    (this gets the quarterly and returns the life span of the stream in years and * by 12)
    IF(F$66<=(IFERROR(INDEX(Setup!$B$63:$N$77,SMALL(IF(Setup!$B$63:$B$77=$B102,ROW(Setup!$L$63:$L$77)-62),ROW($A$1)),13),"")*12
    (adding the launch month)+IFERROR(INDEX(Setup!$B$63:$M$77,SMALL(IF(Setup!$B$63:$B$77=$B102,ROW(Setup!$M$63:$M$77)-62),ROW($A$1)),12),""))
    (Now we if current month < need to offset and have number of columns for offset)
    (True <= offset needed so sum from current column to every third cell to column C.),E102+IFERROR(INDEX($B$68:D$82,SMALL(IF($B$68:$B$82=$B102,ROW(D$68:D$82)-67),ROW($A$1)),E$66),""),0),0),

    (Current month is greater than life span + intro month so sum from current column, every third cell back life (span + intro month) columns)

    Yes, this is an array formula.

  17. Hi,

    I really need help! What does: =SUM(''!$B$9) means? From where does it pic up the figures?

    Thanks you in advance,

    BR/Helena

  18. Very clear and informative! Thank You!

  19. Dear Madame,

    I have following query

    A B C D E
    1 A/C HEAD 1/1/16 8/1/16 16/1/16 24/1/16
    3 PAYROLL 100 200 300 400
    4 CREDITORS 500 600 700 900
    5 LOANS 700 450 350 250

    ABOVE DATE I WISH TO CONVERT VERTICALY IN A SHEET LIKE BELOW
    A B C D E F G H
    1 1/1/16 2/1/16 3/1/16 4/1/16 5/1/16 6/1/16 7/1/16 8/1/16
    2
    3
    4
    5
    USING FORMULA THE DATA TO BE POSTED ACCORDING TO THE COLUMN HEADERS I.E DATES

    BEST REGARDS,
    NITIN SHAH

  20. I am trying to get a result that students have got above 60 among all the students where in the same column, there will be 40and50 and 60, absentees and fail student are there among these i trying to fetch a result that student like First class, Second class, and third class. pl. send answer as soon as possible.

  21. hello,

    your site is great and i found it very useful.

    would you have an examples of calculations such as this: =SUMIF([Timeline],"<="&[@Timeline],[Weighted Forecast]).

    Thanks a lot,

    Ziv

    1. Hello, Ziv,

      For us to be able to assist you better, please describe your task in more detail and give more examples.

  22. Dear, I have amount in cell A Rs:642. I need formula that show in next cell (B) only that amount multiply by 25.

    For example: A B C A B C
    642 625 17 366 350 16

    1. =sum(A1*25) this will do it for you.

  23. Never mind, I figured it out.

  24. I have a sheet with game scores on them. One column is the name of the visiting team, another is the visting teams score, and another is the home teams score. I haven't been able to figure out how to get the number of times the visiting team's score is greater than the home team's score when the visiting team is a certain team. For example, when the visiting team (col A) is Team A, how many times is col B (visiting team score) greater than col C (home team score).

    This seems simple to do, but I've yet to hit upon the right syntax.

  25. Beautiful article. Describes the issue very clearly. Thanks.

  26. Is there a way to combine an array's contents into one cell?
    Ex. {"One","Two","Three"} in one cell as OneTwoThree.
    If so, can this be done: One, Two, Three
    in a single cell from that array?

    If not, I'll write a UDF for what I'm looking to do.

  27. Hi Svetlana,
    Your article, methinks, is the best to start learning Array Formulas.
    Great job!
    Shall I expect an article that describes
    *Add-ins,
    *Simplifying big formulas into small etc.?

    1. Hi Prasad,

      Thank you so much for your kind feedback and the idea. I will give it a thought :)

  28. I like your site very much useful i found it very useful

  29. Very informative article which I have benefited greatly from. Thank you for sharing.

    Nova Scotia, Canada

      1. this formula worked for me =SUM((LEN(C5:AF5)-LEN(SUBSTITUTE(C5:AF5, AH3,"")))/LEN(AH3))

        but when I Drag this formula for the below column.... this formula doesn't apply..... why is that..... this is exactly what I was looking for but at dragging the formula from top to bottom the formula changes and from C the formula goes to D and from AF5 it goes to AF6

        Example :

        Top Column = =SUM((LEN(C4:AF4)-LEN(SUBSTITUTE(C4:AF4, AH3,"")))/LEN(AH3))

        when I drag formula from top to bottom of the spreadsheet

        the formula changes to

        =SUM((LEN(C5:AF5)-LEN(SUBSTITUTE(C5:AF5, AH4,"")))/LEN(AH4))

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)