# Array SUMIF formula with OR formula condition

#### Iris14

##### New Member
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 !!!

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

##### MrExcel MVP
Are you aware that your ranges are all actually single cell references?

#### Iris14

##### New Member
Are you aware that your ranges are all actually single cell references?

Yes i do, that is just an example. Please treat that as a column of cells with different amounts

##### MrExcel MVP
Yes i do, that is just an example. Please treat that as a column of cells with different amounts

Care to word concisely the problem you want to solve? Mention the ranges and the conditions these ranges must meet, etc.

#### Iris14

##### New Member

Care to word concisely the problem you want to solve? Mention the ranges and the conditions these ranges must meet, etc.

Hello Aladin, thanks for getting back to me

the conditions are to add up column H if
- the last 10 character of column I said (Accruals), then add up column H OR
- column C said "Dividend Receipt" and either column E or Column F is zero.

#### pgc01

##### MrExcel MVP
Hi

Try in D16:

=SUMPRODUCT((RIGHT(I\$17:I\$100,10)="(Accruals)")+(C\$17:C\$100="Dividend Receipt")*(ISNUMBER(E\$17:E\$100)*(E\$17:E\$100=0)+ISNUMBER(F\$17:F\$100)*(F\$17:F\$100=0)>0),H\$17:H\$100)

#### Iris14

##### New Member

Hi

Try in D16:

=SUMPRODUCT((RIGHT(I\$17:I\$100,10)="(Accruals)")+(C\$17:C\$100="Dividend Receipt")*(ISNUMBER(E\$17:E\$100)*(E\$17:E\$100=0)+ISNUMBER(F\$17:F\$100)*(F\$17:F\$100=0)>0),H\$17:H\$100)

BCDEFGHIJ
15
16Result:140
1710
1820XXX (Accruals)
1930
2040
21Dividend Receipt050
22Dividend Receipt60
23Dividend Receipt070
24Dividend Receipt80
2590
26
[book1]Sheet2

<tbody>
</tbody>

 Date Currency Transaction Description Account Reference Difference Base Amount Comments 1/31/2014 DKK Dividend Receipt ABC 0.00 19,268.55 (19,268.55) (3,482.38) Adjustment (Accruals)

<tbody>
</tbody>

Hello PGC and Aladin, what I mean double counting is that the condition with (RIGHT(I\$17:I\$100,10)="(Accruals)") and SUMPRODUCT(--(C\$17:C\$17 = "Dividend Receipt"),--((E\$17:E\$17 = 0) + (F\$17:F\$17 = 0)),(H\$17:H\$17)) are accounting for the same line, therefore the amount (3,482.38) are being duplicated. I would like to put the two conditions in a OR formula, so that it won't be duplicated.

I have tried {
=SUM(IF(OR(RIGHT(I17:I18,10)="(Accruals)",AND(C17:C18="Dividend receipt",OR(E17:E18=0,F17:F18=0))),H17:H18,0))} and for some reasons... it is still duplicating what I want..

#### pgc01

##### MrExcel MVP
I see.

In my example try:

=SUMPRODUCT(--((RIGHT(I\$17:I\$100,10)="(Accruals)")+(C\$17:C\$100="Dividend Receipt")*(ISNUMBER(E\$17:E\$100)*(E\$17:E\$100=0)+ISNUMBER(F\$17:F\$100)*(F\$17:F\$100=0)>0)>0),H\$17:H\$100)

#### Iris14

##### New Member
I see.

In my example try:

=SUMPRODUCT(--((RIGHT(I\$17:I\$100,10)="(Accruals)")+(C\$17:C\$100="Dividend Receipt")*(ISNUMBER(E\$17:E\$100)*(E\$17:E\$100=0)+ISNUMBER(F\$17:F\$100)*(F\$17:F\$100=0)>0)>0),H\$17:H\$100)

Hi PGC, yes i did try but it is still not duplicated the amount that I want, coz the (RIGHT(I\$17:I\$100,10)="(Accruals)") and the (C\$17:C\$100="Dividend Receipt")*(ISNUMBER(E\$17:E\$100)*(E\$17:E\$100=0)+ISNUMBER(F\$17:F\$100)*(F\$17:F\$100=0)>0)>0) are still being double counted

#### pgc01

##### MrExcel MVP
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.

Replies
7
Views
120
Replies
15
Views
224
Replies
7
Views
73
Replies
2
Views
167
Replies
2
Views
26