Good morning all. I am hoping NOT to have to redo all my formulas to fix this. It would would be so very much easier if my name range would look at data in two columns as one.
I have a DATA sheet called 'ALL DATA'. I update the data sheet monthly. I export data from my mainframe and never had an issue until this month when the Sales department started selling a product that we never sold before. It populates the 'ALL DATA' sheet in a separate column. It has been blank for about two years. (Now they get a bigger commission to sell it)
On the 'ALL DATA' sheet in column "W" is the figure from WARRANTY sales. (I am a car dealer). In column "Z" is the figure from ALARM systems. I have named the RANGE W2:W4590 as WARR.
I use the following formula '=SUMPRODUCT((STORE="BLUE")*(NEWUSED=D32)*(NAME=C32)*(WARR)*(MONTH=$C$25))' and it works (worked) great. Store in the case is literal. Ranges on the 'ALL DATA' sheet are NEWUSED and NAME. All the named ranges are all from Col2:Col4590.
Here's where I screw this currently working sheet up. I tried to re-define the range WARR which is currently ='ALL DATA'!$W$2:$W$4590 to ='ALL DATA'!$W$2:$W$4590,'ALL DATA'!$Z$2:$Z$4590.
When I do this, if I just do a quick test like =SUM(WARR) I get the correct figure. If I click the RANGE NAME both Columns highlight to show the WARR range.
However, the formula that worked so perfectly before ('=SUMPRODUCT((STORE="BLUE")*(NEWUSED=D32)*(NAME=C32)*(WARR)*(MONTH=$C$25))' now returns a #VALUE.
I deleted the Range name and tried again but get the same result.
If I could get the NameRange WARR to work within my SUMPRODUCT formula, it would save me hours and hours of work. Plus, I am a semi-idiot so I will likely screw up more cells when I fix it.
tAHNSK IN ADVANCE,
Creeker69
Turtle Creek, Pa
I have a DATA sheet called 'ALL DATA'. I update the data sheet monthly. I export data from my mainframe and never had an issue until this month when the Sales department started selling a product that we never sold before. It populates the 'ALL DATA' sheet in a separate column. It has been blank for about two years. (Now they get a bigger commission to sell it)
On the 'ALL DATA' sheet in column "W" is the figure from WARRANTY sales. (I am a car dealer). In column "Z" is the figure from ALARM systems. I have named the RANGE W2:W4590 as WARR.
I use the following formula '=SUMPRODUCT((STORE="BLUE")*(NEWUSED=D32)*(NAME=C32)*(WARR)*(MONTH=$C$25))' and it works (worked) great. Store in the case is literal. Ranges on the 'ALL DATA' sheet are NEWUSED and NAME. All the named ranges are all from Col2:Col4590.
Here's where I screw this currently working sheet up. I tried to re-define the range WARR which is currently ='ALL DATA'!$W$2:$W$4590 to ='ALL DATA'!$W$2:$W$4590,'ALL DATA'!$Z$2:$Z$4590.
When I do this, if I just do a quick test like =SUM(WARR) I get the correct figure. If I click the RANGE NAME both Columns highlight to show the WARR range.
However, the formula that worked so perfectly before ('=SUMPRODUCT((STORE="BLUE")*(NEWUSED=D32)*(NAME=C32)*(WARR)*(MONTH=$C$25))' now returns a #VALUE.
I deleted the Range name and tried again but get the same result.
If I could get the NameRange WARR to work within my SUMPRODUCT formula, it would save me hours and hours of work. Plus, I am a semi-idiot so I will likely screw up more cells when I fix it.
tAHNSK IN ADVANCE,
Creeker69
Turtle Creek, Pa