Array SUMIF formula with OR formula condition

[Iris14]

Hello, I have a sumproduct array formula before but it is double counting the two conditions...

= SUMPRODUCT(--(RIGHT(I$17:I$17,10) = "(ACCRUALS)"),(H$17:H$17)) + SUMPRODUCT(--(C$17:C$17 = "Dividend Receipt"),--((E$17:E$17 = 0) + (F$17:F$17 = 0)),(H$17:H$17))

Therefore, I am thinking a way to put a or condition in between...

I have two conditions in the excel formula


1. =ISNUMBER((C$17:C$17="Dividend Receipt")--((E$17:E$17=0)+(F$17:F$17=0)))
2. =(RIGHT(I$17:I$17,10) = "(ACCRUALS)")

which works well individually giving the answer as TRUE in excel.

I would like to build one formula to incorporate the two in a OR condition.

I have tried the followings, but it doesn't work...

Is there anyone who can help?

=SUM(IF(OR((--RIGHT(I$17:I$17,10)="(Accruals")),(ISNUMBER(--(C$17:C$17="Dividend Receipt")--((E$17:E$17=0)+(F$17:F$17=0)))))=TRUE,H$17:H$17,0)

Thanks a lot !!!

 

[Iris14]

It works OK for me.

To test I set both columns E and F to 0 and the last characters in column I equal to "(Accruals)".

It did not double count.

	B	C	D	E	F	G	H	I	J
15
16		Result:	140
17							10
18							20	XXX (Accruals)
19							30
20							40
21		Dividend Receipt		0	0		50	XXX (Accruals)
22		Dividend Receipt					60
23		Dividend Receipt		0	0		70	XXX (Accruals)
24		Dividend Receipt					80
25							90
26
[book1]Sheet2

<tbody>
</tbody>

It is working. PGC, would you mind explaining why it would work putting the ISNUMBER function and the >0 condition? Thanks a lot for your help !

 

[Iris14]

It works OK for me.

To test I set both columns E and F to 0 and the last characters in column I equal to "(Accruals)".

It did not double count.

	B	C	D	E	F	G	H	I	J
15
16		Result:	140
17							10
18							20	XXX (Accruals)
19							30
20							40
21		Dividend Receipt		0	0		50	XXX (Accruals)
22		Dividend Receipt					60
23		Dividend Receipt		0	0		70	XXX (Accruals)
24		Dividend Receipt					80
25							90
26
[book1]Sheet2

<tbody>
</tbody>

Hi PGC, i think it is working. Would you please explain why adding the Isnumber and the >0 condition would make the amount not being duplicated? Thanks a lot for your help !!!

 

[pgc01]

Hi PGC, i think it is working. Would you please explain why adding the Isnumber and the >0 condition would make the amount not being duplicated? Thanks a lot for your help !!!

Hi

1 - You need the IsNumber() because if a cell is empty, the test against 0 is True.

Try this.

- select A1 and press Delete to clear A1
- in B1 write the formula

=(A1=0)

You'll see that the result is True. This is why if you want to test for 0 you should also test if the cell contains a number or else any empty cell will throw your calculations off.

2 - You need the ">0" to avoid double counting.

The way to perform an OR() in an array formula is to add the conditions. The problem is that the result you want is:

- if every condition is False you want the result to be 0
- if any condition in True you want the result to be 1, no matter how many conditions are True

For ex., for 2 conditions

(A1:A10="A")+(B1:B10="B")

You have 3 possible cases

1 - both conditions are false. The result is 0. OK
2 - one of the conditions is True and the other False. Result is 1. OK
3 - both conditions are True. Result is 2. Error. You want 1.

This is why you use the ">0", because you are only interested if the result of the testing is different from 0.

--((A1:A10="A")+(B1:B10="B")>0)

Now you have only 2 possible values, either both conditions are false and you get 0 or some conditions are True, no matter how many, and you get 1.

Hope it's clear.