• Status: Solved
• Priority: Medium
• Security: Public
• Views: 74

# IS there a more compact way of writing an OR formula in Excel 2010?

Is there a more compact way of writing the formula below? I would think that writing "Total Due" 3 times shouldn't be necessary. This is not VBA, it's just a formula in one of my cells.

``````=IF(OR(INDIRECT(ADDRESS(ROW()-1,COLUMN()))="Total Due",INDIRECT(ADDRESS(ROW()-2,COLUMN()))="Total Due",INDIRECT(ADDRESS(ROW()-3,COLUMN()))="Total Due"),I17+L17+N17,"")
``````
0
shampouya
• 3
• 2
• 2
1 Solution

VBA ExpertCommented:
You could use a different function.

For example, MATCH.

0

Commented:
Like Norie Said..You can use different function...

if you like a shorter one version then you can use countif with offset like this...

Saurabh...
0

VBA ExpertCommented:
Saurabh

I was going to use OFFSET but I couldn't quite figure how it would fit with the ADDRESS and INDIRECT.

I can see how to do it now.

0

Commented:
Glad to be of help buddy..and after reading your formula and rechecking what he was trying to do..realized he want to check 3 rows not 4 rows..so this will become...

Saurabh...
0

Commented:
I realize that the question has been answered, but I feel uncomfortable whenever the ADDRESS function is part of the answer. There is invariably a better way.

For example, if the formula is going in cell E13, you could use:
=IF(COUNTIF(E10:E12,"Total Due")>0,I17+L17+N17,"")

If you are installing the formula using VBA, I suggest using RC notation to avoid the need to know the address of the target cell:
=IF(COUNTIF(R[-3]C:R[-1]C,"Total Due")>0,R17C9+R17C12+R17C14,"")
If the above formula is put in cell E13 using the .FormulaR1C1 property of that range, you would get:
=IF(COUNTIF(E10:E12,"Total Due")>0,\$I\$17,\$L\$17+\$N\$17,"")
0

VBA ExpertCommented:
Byundt

I agree with you about using ADDRESS but since we didn't know which cell the formula was to go in and VBA was ruled out I'm not sure there's another way.
0

Commented:
Norie,
=IF(COUNTIF(INDEX(\$1:\$1048576,ROW()-3,COLUMN()):INDEX(\$1:\$1048576,ROW()-1,COLUMN()),"Total Due")>0,I17+L17+N17,"")

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.