View original (French)

SUMIFS issue with an "OR"

Solved
makks Posted messages 18 Status Member -  
makks Posted messages 18 Status Member -
Hello,

I am reaching out for your help because I have a small problem with my formula below:
=SUMIFS(Data!P:P, Data!K:K, "", Data!D:D, 1, Data!L:L, OR("V", "E"))

Let me explain, I would like to sum up column P from the "Data" sheet when first, column K is empty, then when column D equals 1, so far so good.
But I would like my third criterion to take an "OR," where my column L contains either V or E.

If someone can help me, I would be very grateful, and I apologize if the question has already been asked.

Best regards.

2 answers

  1.
    DjiDji59430 Posted messages 4278 Registration date   Status Member Last intervention   717
     
    Hello,

    Or with this:

    =SUMPRODUCT((Data!P:P)*(Data!K:K="")*(Data!D:D=1)*((Data!L:L="V")+(Data!L:L="E")))
    13
    1. Theo.R Posted messages 585 Status Member 31
       
      makks I think DjiDji59430's solution does the trick!!

      Thank you DjiDji, I wasn't familiar with SOMMEPROD ;)
      0
    2. makks Posted messages 18 Status Member 1
       
      Hm, I’m going to look into SOMMEPROD, but the formula above shows me "VALUE#", I’ll keep you posted if I find it, I'm currently trying to correct it. ;)
      1
    3. makks Posted messages 18 Status Member 1
       
      Ok so, this formula works:
      =SUMPRODUCT((Data!K:K="")*(Data!D:D=1)*(Data!C:C=2010)*((Data!L:L="V")+(Data!L:L="E")))
      But I just need to multiply it by the amount which is in column P (Data!P:P), it shows 8 instead of 10 (2 cases out of 8 are at 2 instead of 1).

      PS: I added the year 2010, don’t pay attention to it, it doesn’t change anything.
      0
    4. makks Posted messages 18 Status Member 1
       
      Ok, the problem was that my column P had text in its first row, thanks everyone!!
      0