Sum with Multiple IF AND OR Conditions

[Argh]

Can't seem to find the correct way to put this in a formula, any help would be very much appreciated. It is counting each row if it meets the conditions.

Basically:

SUM (IF(A AND B) AND IF((C) AND/OR (D) AND/OR (E)))

Complexly:

SUM (IF(A1:A2000="value" AND B1:B2000="value") AND IF(C1:C2000="value" AND/OR D1:D2000="value" AND/OR D1:D2000="value"))

 

[Argh]

False alarm, doesn't work - still need help.

 

[T. Valko]

Can't seem to find the correct way to put this in a formula, any help would be very much appreciated. It is counting each row if it meets the conditions.

Basically:

SUM (IF(A AND B) AND IF((C) AND/OR (D) AND/OR (E)))

Complexly:

SUM (IF(A1:A2000="value" AND B1:B2000="value") AND IF(C1:C2000="value" AND/OR D1:D2000="value" AND/OR D1:D2000="value"))

You have AND/OR D1:D2000="value" repeated.

Well, that "pseudo formula" still isn't clear (to me).

Is this a good description:

if A = this and If B = this and if C or D = this

What version of Excel are you using?

 

[jeffreybrown]

Based on the Sumproduct you just posted, could this be what you need...

=SUMPRODUCT(--(A1:A2000="value"),--(B1:B2000="value"),--(C1:C2000="value"),--(D1:D2000="value"))

 

[T. Valko]

As always, after working for hours and then posting on forums, I finally get it right. Here is the code for anyone else who hits a similar wall:

SUMPRODUCT(A1:A2000="value")*('B1:B2000="value")*(OR(C1:C2000="value",D1:D2000="value",E1:E2000="value")))"

If that returns a correct result it's just "dumb luck".

The OR function isn't doing what you think it's doing.

In C, D and E is "value" the same? Like this:

(C1:C2000="X",D1:D2000="X",E1:E2000="X")

 

[Argh]

Sorry I should justify my formula:

SUM (IF(A1:A2000="a" AND B1:B2000="b") AND IF(C1:C2000="c" AND/OR D1:D2000="d" AND/OR E1:E2000="e"))

The value that the lookups are equalling are not important, just the fact that there is a lookup on multiple columns such as this (A&B)&(C&/D&/E)

Two conditions:

A and B have to contain the values assigned to them
One or more of C, D or E have to contain the values assigned to them

If so, counter ++
Else, nextrow

Cases:
Row 1: A contains its required value, B contains its required value BUT C, D or E do not, counter stays at 0.
Row 2: A contains its required value, B contains its required value, C contains its required value, BUT D or E do not, counter++
Row 3: A contains its required value, B contains its required value, D contains its required value, BUT C or E do not, counter++
Row 4: A contains its required value, B contains its required value, C contains its required value, D contains it's required value BUT E does not, counter++
Row 5: A contains its required value, B does not, nextrow

 

[Argh]

Based on the Sumproduct you just posted, could this be what you need...

=SUMPRODUCT(--(A1:A2000="value"),--(B1:B2000="value"),--(C1:C2000="value"),--(D1:D2000="value"))

I'm interested in what the '--' bit does?

 

[jeffreybrown]

It's a way to convert a True/False into a 1/0.

Take a look here http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[T. Valko]

Sorry I should justify my formula:

SUM (IF(A1:A2000="a" AND B1:B2000="b") AND IF(C1:C2000="c" AND/OR D1:D2000="d" AND/OR E1:E2000="e"))

The value that the lookups are equalling are not important, just the fact that there is a lookup on multiple columns such as this (A&B)&(C&/D&/E)

Two conditions:

A and B have to contain the values assigned to them
One or more of C, D or E have to contain the values assigned to them

If so, counter ++
Else, nextrow

Cases:
Row 1: A contains its required value, B contains its required value BUT C, D or E do not, counter stays at 0.
Row 2: A contains its required value, B contains its required value, C contains its required value, BUT D or E do not, counter++
Row 3: A contains its required value, B contains its required value, D contains its required value, BUT C or E do not, counter++
Row 4: A contains its required value, B contains its required value, C contains its required value, D contains it's required value BUT E does not, counter++
Row 5: A contains its required value, B does not, nextrow

Let's assume this is your data:

Book1
	A	B	C	D	E
1	A	B
2	A	B	C		E
3		B	C	D
4	A	B		D	E
5	A	B	C	D	E
6	A	B	C	D	E
7	A	B	C
8	A	B	C	D	E
9	A	B			E
10	A		C	D	E
Sheet1

This formula:

=SUMPRODUCT(--(A1:A10="A"),--(B1:B10="B"),SIGN((C1:C10="C")+(D1:D10="D")+(E1:E10="E")))

Returns a count of 7.

 

[Argh]

=SUMPRODUCT(--(A1:A10="A"),--(B1:B10="B"),SIGN((C1:C10="C")+(D1:D10="D")+(E1:E10="E")))

Thankyou so much mate, that worked like a charm. Much appreciated!