or statement

bluepenink

Well-known Member
Joined
Dec 21, 2010
Messages
585
hello all

can someone tell me why this formula i have is not working?

=IF(OR(ROW()-12>SUM(DistrictSum),ROW()-12>SUM(AEDistSum)),"",LEFT('- "District" -'!$A$3,1)&'- "District" -'!$C$3&LEFT('- "District" -'!$A$4,1)&'- "District" -'!$C$4)

it works fine with (districtsum)

but when i include the "OR" statement for it to check in another worksheet range "AEDistSum" it does not pull all the numbers.

any suggestions?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
but when i include the "OR" statement for it to check in another worksheet range "AEDistSum" it does not pull all the numbers.
In messing around with SUMS from named ranges on other worksheets within the same workbook, it seems to work fine for me.

So perhaps there is a problem with how you have written your formula. Can you explain exactly what it is this formula is supposed to be doing?
 
Upvote 0
hello Joe4

basically,

If either the sum from districtsum or the sum from AE Dist Sum is less than the row - 12, your formula is being applied...i think
this was my original formula with the row-12

First I created a defined name DistrictSum = X21,X31,X41,X51,X61 to simplify things.

Then in D13 of Win Sheet dragged down

=IF(ROW()-12>SUM(DistrictSum),"",LEFT('- "District" -'!$A$3,1)&'- "District" -'!$C$3&LEFT('- "District" -'!$A$4,1)&'- "District" -'!$C$4)

i THEN wanted to capture the data for another worksheet called AE district, so i applied the same concept and put a "OR"

but its only showing 1 value, when it should show 6 in total? ur thoughts?
 
Last edited:
Upvote 0
It seems rather odd to me that you would be comparing a SUM to the current row number the formula resides in.
That is what ROW()-12 does. It subtracts 12 from the current row number than the formula resides in.

Is that correct? Are you really comparing a SUM to a calculation on a row number, or are you trying to compare it to a value in the row?
 
Upvote 0
hello

Basically, i now have two defined ranges:

district
DistrictSum = X21,X31,X41,X51,X61
AE district
AEDistSum = X21,X31,X41,X51,X61

each one of the above rows would contain a # (i.e. 1-10)

in my formula basically, i want to know if AEDistSum and DistrictSum contain a value i.e. 2 in total,
then show

LEFT('- "District" -'!$A$3,1)&'- "District" -'!$C$3&LEFT('- "District" -'!$A$4,1)&'- "District" -'!$C$4)
-which is basically the month and week the # is comprised in

so the 2 would total the sum from both the AE and district worksheet? do u follow? thxs
 
Upvote 0
basically

its row - 12 b/c my data starts at row 13

so its saying

current row - 12 > sum(district) then do AS REQUIRED

it does that fine...

but when i add inthe criteria "OR"....it messes things up? any idea why?
 
Last edited:
Upvote 0
I am still not real clear. Unfortunately, I am unable to download files from the internet from my current location (corporate policy), so I cannot look at your file.

There are some tools you can use to post screen prints. They are mentioned here: http://www.mrexcel.com/forum/showthread.php?t=508133.

Otherwise, it might be best to post an example. I think we just need to following details to analyze your formula:

- What values are in cells A3, A4, C3, and C4?
- What values do SUM(DistrictSum) and SUM(AEDistSum) return?
- What row are you placing this formula in?
- What is your expected result?
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top