Comments on: How to use SUMIF in Excel with multiple OR conditions

Microsoft Excel has a special function to sum cells with multiple conditions and the AND logic - the SUMIFS function. In some situations, however, you may need to sum with multiple OR criteria. And this is when the SUMIF function comes in handy. Continue reading →

Comments page 2. Total comments: 70

  1. Thanks for your helpful articles. Very clear and well presented. I am trying to use the SUMIF formula to extract a total value from 1 column using a qualifying value detailed in another column between defined limits eg 1.60 to 2.0. I tried using "2.0" which gives an error message. I then tried SUMIFS but this appears to need values in 2 different columns to work. Is there a further article that would help here please? You can see the spreadsheet on this link if it helps. https://docs.google.com/spreadsheets/d/1BgZqQ-R5PGkoRtVwStJN-pl9TH57LIBDqpZPvA6WIoU/edit?usp=sharing The cell in question is D6 and E5 will need a similar solution ( I have used a simple work around for the time being but this will only work if I do not want to add further analysis). Thanks and regards Peter

  2. "Count the number of unique IDs for which "received" is written." Exactly. Now you have undestood. Thank you.

  3. The source data are below and the expected result is 1.

    For the data below in two column, the formula result should be 1 eventhogh the ID5596326 appears 6 times and "received" 2 times.

    5596326 attempted
    5596326 delivered
    5596326 received
    5596326 received
    5596326 sent
    5596326 Wrong Number

    1. Hello!
      Your explanations are still not very clear. I'll try to guess. Count the number of unique IDs for which "received" is written.

      =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,B2:B100="received"))), 0)

      You can read more about how to calculate unique values in this article.

  4. I would like to request your assistance in developing an Excel formula.

    I would like to count only the first occurrence in which an ID number appears in a column and also the text "received" appears in the same record (row) for that ID.

    For example, if ID 5596326 occurs 6 times and "received" occurs 2 times for that ID.

    For this ID, the answer would be 1.

    What would be the formula?

    This has really stumped me, and I would appreciate your help.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(COUNTIFS(A:A,A2,B:B,"received")>0,1,"")

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      1. If the data, with a header row, consists of 100 records, with an ID column and a results column, how is the range or ranges specified?

        1. Hi,
          If I got you right, the formula below would help you with your task:

          =IF(COUNTIFS($A$2:$A$101,A2,$B$2:$B$101,”received”)>0,1,””)

          After that, you can copy this formula down along the column.

          1. This formula =IF(COUNTIFS($A$2:$A$101,A2,$B$2:$B$101,”received”)>0,1,””) does not answer the question that I am asking. This formula results in a count of 1 on a single row for an ID in one colum and when "received" appears in an other column.

            But my question is different. Take this example in which the ID is identical, but "received" appears twice. The same ID might appear 10 times and "received" 4 times. The problem is the same.

            How for a single ID that may appear many times can a formula be written to produce one distinct result of 1 for an ID when the ID and "received" may appear more than once?

            So the result that I am looking for is not 1 per row in which "received" appears, but 1 per distinct ID when the ID and "received" may appear multiple times as in the example.

            5596326 attempted
            5596326 delivered
            5596326 received
            5596326 received
            5596326 sent
            5596326 Wrong Number

            1. Hello!
              Sorry, I do not fully understand the task. Write an example of the result you want to get. You sent a file with an example of source data, but there is no example of the expected result. Do you want to get a list of IDs? I cannot guess.

            2. The result that I am looking for can be achieved by filtering the workbook on "received," copying the filtered result to another worksheet, and remove duplicate rows by ID. Then I would just look at the resulting number of rows in teh worksheet and that would be the total number of distinct cases for a single ID and "received" having occurred at least once.

              But I am looking for a formula to do that instead of going through the filtering and unduplicating procedure.

              1. The result you need is
                =IF(COUNTIFS($A$1:$A2,A2,$B$1:$B2,”received”)>0,"",IF(COUNTIFS($A$2:$A$101,A2,$B$2:$B$101,”received”)>0,1,""))

  5. i am trying to set formulae for calculating figures in various sheets for dated 1st to 30th of month in which all the figures are in same column i,e for example A5 of sheet 1,sheet 2.

    please suggest me the formulae

  6. Dear Sir,

    RID NAME Jan 01 Jan 02 Jan 03 Jan 04 Jan 05 Jan 06 Jan 07 Jan 08 upto Dec 31
    10001 Name 1 # # # # P A A A P P
    10002 Name 2 # # # # P A A A P P
    10003 Name 3 # # # # P A A A P P
    10004 Name 4 # # # # P A A A P P
    10005 Name 5 # # # # P A A A H H
    10006 Name 6 # # # # P A A A P P

    # HOLIDAY
    H Half day
    A absent
    P present

    RID MONTH No of Days Present
    10001 JANUARY ?

    How to get no of days present with given parameters.

    Thanks - D. Phani Kumar

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Thank you!

      1. Dear Sir,

        if some body asks how may days absent/present in a month or total year, to answer this, i will take his RID and MONTH / FROM TO DATES. SHEET holds current years attendance of employees both new and old.

        RID 01/01/2020 02/01/2020 …. 31/12/2020 # HOLIDAY
        1001 P P P H Half day
        1002 # H A A absent
        1003 P P P P present

  7. Hi,

    I have the following formula

    =IF(ISNUMBER(SEARCH("fred",B12))*ISNUMBER(SEARCH("saturday",L12)),E12*$M$2*$R$5,"")

    This give the desired output for fred and Saturday but I need to add the same again but for fred and Sunday, which would also change the refernce M2 to R2. So the equiv' formula for fred and Sunday is

    =IF(ISNUMBER(SEARCH("fred",B12))*ISNUMBER(SEARCH("sunday",L12)),E12*$R$2*$R$5,"")

    How do I combine the two?

    Thanks

    Ian

    1. Hello!
      Add a condition to test the value of cell L12

      =IF(ISNUMBER(SEARCH(“saturday”,L12)), IF(ISNUMBER(SEARCH(“fred”,B12))*ISNUMBER(SEARCH(“saturday”,L12)),E12*$M$2*$R$5,””), IF(ISNUMBER(SEARCH(“fred”,B12))*ISNUMBER(SEARCH(“sunday”,L12)),E12*$R$2*$R$5,””))

      I hope it’ll be helpful.

  8. Thanks for good guidance

  9. 12D, 6N, R, N, L, D, SL, R, N, D, 9N. CAN YOU PLEASE HELP ME TO SUM THIS ALPHANUMERICS, BUT BY OMITTING THE LETTERS I SHOULD GET THE ANSWER AS = 27.

    THANKS A LOT

    1. Hello!
      To extract and sum all single and double digit numbers from text, use the formula

      =SUM(IF(ISNUMBER(--MID(A1,ROW($1:$93),2)),--MID(A1,ROW($1:$93),2),""))

      Hope this is what you need.

  10. Hi!

    Thank you for the details and steps above!

    I am having some issues with the Sum+sumif and sumproduct formulas, when I enter in the formula with the range E1:E3 I get a '0' value returned. I went back to the steps again and continuously checked the formula, but still get the '0' value.

    What other areas/values should I check?

    Thank you,

    E

    1. Hello!
      The information you provided is not enough to understand your case and give you any advice, sorry. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

    2. Hi!

      It's difficult to say anything certain without seeing your data and formula. For starters, please check if the numbers you are summing are really numbers and not numbers stored as text. If you add them up with a simple formula like =A1+A2+A3..., does it work? If this simple calculation works fine, then the problem is definitely with the Sum+Sumif formula.

      You can send your source data and expected result to us at support@ablebits.com, and we will try to figure it out.

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 :)