CODEWITHSUNDEEP
excelexcel-formulasumifs
Jovica
Jovica

Reputation: 444

SUMIFS with AND and OR

I've tried, I think most of the answers here, but I can not get them to work in my situation.

Translated to text, my problem would looked like this :

Sum Y:Y column with

Put in SUMIFS function, in theory, it would looked like this SUMIFS(Y:Y,B:B,"15*",T:T,">0",or(U:U,"=0", U:U>V:V)), but of course it doesn't work like that.

Also I've tried SUMPRODUCT and SUM(SUMIF...but I don't know how to translate what I like to accomplish into these two.

So please, if someone knows how to make this work. And some explanation please.

Thanks.

Upvotes: 1

Views: 2826

Answers (2)

Mario J.
Mario J.

Reputation: 51

I would do this like this:

1. Add new column for your condition 3

Use another column, for example Z:Z wher you can put condition3. For example, formula in Z2=OR(U2>0;U2;V2).

This formula will give you values TRUE or FALSE as a result.

2. Then you can base condition 3 on values in new column

Now you SUMIFS formula can be: =SUMIFS((Y:Y;B:B;"15*";T:T;">0";Z:Z;TRUE)

I checked this on your example and it works.

Upvotes: 0

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

Here's SUMPRODUCT based solution which shall work for you.

=SUMPRODUCT(--(LEFT(B:B,2)="15"),--(T:T>0),((U:U=0)+(U:U>V:V)),Y:Y)

Caution: Apply complete column references only if necessary as it will slow down your sheet considerably. It is advisable to have a sufficiently large range (but not whole column references) as each column will have million cells to check.

Also note Barry's query about column V being negative has significance on the formula I have posted as it will calculate 2 TRUE conditions as 2 and therefore lead to erroneous result.

Upvotes: 2

Related Questions