View original (French)

SUMIFS issue with an "OR"

Solved
makks Posted messages 18 Status Membre -  
makks Posted messages 18 Status Membre -
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 réponses

Réponse 1 / 2
DjiDji59430 Posted messages 4277 Registration date   Status Membre Last intervention   716
 
Hello,

Or with this:

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

Thank you DjiDji, I wasn't familiar with SOMMEPROD ;)
0
makks Posted messages 18 Status Membre 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
makks Posted messages 18 Status Membre 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
makks Posted messages 18 Status Membre 1
 
Ok, the problem was that my column P had text in its first row, thanks everyone!!
0