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.



[TABLE="width: 2"]
<tbody>[TR]
[TH] [/TH]
[TH="width: 30, align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="width: 30, align: center"]D[/TH]
[TH="width: 30, align: center"]E[/TH]
[TH="width: 30, align: center"]F[/TH]
[TH="width: 30, align: center"]G[/TH]
[TH="width: 30, align: center"]H[/TH]
[TH="align: center"]I[/TH]
[TH="width: 30, align: center"]J[/TH]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Result:[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"]10[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"]20[/TD]
[TD="align: left"]XXX (Accruals)[/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"]30[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"]40[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Dividend Receipt[/TD]
[TD="align: right"] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] [/TD]
[TD="align: right"]50[/TD]
[TD="align: left"]XXX (Accruals)[/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Dividend Receipt[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"]60[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Dividend Receipt[/TD]
[TD="align: right"] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] [/TD]
[TD="align: right"]70[/TD]
[TD="align: left"]XXX (Accruals)[/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Dividend Receipt[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"]80[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"]90[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="colspan: 10"] [book1]Sheet2[/TD]
[/TR]
</tbody>[/TABLE]

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.



[TABLE="width: 2"]
<tbody>[TR]
[TH] [/TH]
[TH="width: 30, align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="width: 30, align: center"]D[/TH]
[TH="width: 30, align: center"]E[/TH]
[TH="width: 30, align: center"]F[/TH]
[TH="width: 30, align: center"]G[/TH]
[TH="width: 30, align: center"]H[/TH]
[TH="align: center"]I[/TH]
[TH="width: 30, align: center"]J[/TH]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Result:[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"]10[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"]20[/TD]
[TD="align: left"]XXX (Accruals)[/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"]30[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"]40[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Dividend Receipt[/TD]
[TD="align: right"] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] [/TD]
[TD="align: right"]50[/TD]
[TD="align: left"]XXX (Accruals)[/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Dividend Receipt[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"]60[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Dividend Receipt[/TD]
[TD="align: right"] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] [/TD]
[TD="align: right"]70[/TD]
[TD="align: left"]XXX (Accruals)[/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Dividend Receipt[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"]80[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"]90[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="colspan: 10"] [book1]Sheet2[/TD]
[/TR]
</tbody>[/TABLE]

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.