sumifs work in one cell and shows 0 in other

  • Hello,


    Can you explain what exactly you want to calculate ...???


    Do not forget the difference between using the AND logic ... versus using the OR logic ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello


    i basically want to sum A,G, and D S from column A, my sum range is in column G, sorry my formulas are wong in the test file. but i still getting the same issue


    Code
    1. =SUMIFS(G:G,A:A,"A",A:A,"G",A:A,"D S")


    i did use the below formula in cell k1 and this works, but the above formula does not. i will reupload the file again with the correction in formulas. I just wanna know where i am going wrong because if above formula is wrong then the below one shouldn't work as well


    Code
    1. =SUMIFS(G:G,A:A,"<>A",A:A,"<>G",A:A,"<>D S")
  • This formula:

    =SUMIFS(G:G,A:A,"A",A:A,"G",A:A,"D S")


    is trying to sum column G where column A is "A" and it's "G" and it's "D S" all on the same row. Since column A can't be all three values at once, you get 0. You need an OR condition as Carim suggested, so you need an array of criteria and then to sum the results, like this:


    =SUM(SUMIFS(G:G,A:A,{"A","G","D S"}))

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • thank you for the reply, just for understanding, why the formula works if i use<> , the only difference between the two formulas is that <> is missing



    As explained by Rory, all 3 criteria are located in Column A ... and, as stated in message # 2, in my opinion ...


    you should take the time to fully master the difference between using the AND logic ... versus using the OR logic ...


    You have not explained the exact calculation you want to get ... most probably you need the OR logic ...:)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • The <> logic works because you want each row to not be "A" and to not be "G" and to not be "D S" - i.e. to not be any of the three.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why