Multiple columned Range - gives error after change in NAME Range 1

[creeker69]

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.

Thanks in advance,
Creeker69

 

[SkipVought]

HI,

This won't work.

Simply name WARR as 'ALL DATA'!$W$2:$W$4590 and WARR1 as 'ALL DATA'!$Z$2:$Z$4590

then
[tt]
=SUMPRODUCT((STORE="BLUE")*(NEWUSED=D32)*(NAME=C32)*(WARR)*(MONTH=$C$25))+SUMPRODUCT((STORE="BLUE")*(NEWUSED=D32)*(NAME=C32)*(WARR1)*(MONTH=$C$25))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[byundt]

If you use INDEX to present one column at a time, a SUMPRODUCT formula will work using the two column WARR range:
=SUMPRODUCT((STORE="BLUE")*(NEWUSED=D32)*(NAME=C32)*(MONTH=$C$25)*(INDEX(WARR,,,1)+INDEX(WARR,,,2)))

By way of explanation, the WARR range consists of two non-contiguous areas, one in column W and the other in column Z. The INDEX function uses its third parameter to specify the area. The commas with nothing following them mean that the INDEX function returns all rows and all columns within the specified area.

Brad

 

[SkipVought]

@Brad, nice tip!!! New vistas appear! Possibilities abound!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[creeker69]

Brad,

Thank you so very much! INDEX works perfectly! While I didn't want to change any of my formulas, your solution made the change to the many formulas on the different output sheets pretty simple. I just did a REPLACE (ctrl+H) and then I only had to type your formula the one time. As I only changed the end of the formula, I didn't have to worry about the "NAME = xxx" changes.

I looked into the INDEX function. I was (and really still am) unfamiliar with it. I wonder if I could impose upon once more a solution in this same wrokbook. With your formula change, the dollar figures from either column (W or Z) add up perfectly. Even if there is a value in, let's say W254 and Z254, it adds to the total perfect.

Trying to use the same logic, I get a #VALUE error when I now try to count cells that are not empty. I need to report the total number of WARRanties sold for any given month. If a customer bought both types of WARRanties, we would count as two warranties sold.

My old formula which worked BEFORE the WARR name range became two columns was =SUMPRODUCT((STORE="BLUE")*(NEWUSED="USED")*(NAME=C32)*(WARR<>"")*(MONTH=$C$25)) This just counted the number sold regardless of gross profit. (If it sold for zero profit it was not imported and would have been blank therefore NOT counted.)

As you may have guessed, I am trying to avoid naming ranges WARR1 and WARR2. Though I know for sure it would work, too many modifications would have to be made to the formulas on all the sheets.

Thanks
Creeker69

 

[byundt]

Creeker69,
You might try this variant on your counting formula:
=SUMPRODUCT((STORE="BLUE")*(NEWUSED="USED")*(NAME=C32)*((INDEX(WARR,,,1)<>"")+(INDEX(WARR,,,2)<>""))*(MONTH=$C$25))

Brad

 

[SkipVought]

Creeker, did you even THINK of trying Brad's method? You obviously did not TRY it. Take charge of your situation! This is Tek-Tips and Brad had given you a GREAT tip. I'd have jumped on it and would have said to myself, "I wonder if I could used Brad's INDEX() tip on this next problem?" Then I'd post back to thank Brad and proudly proclaim how I had applied it myself to another SUMPRODUCT()!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[byundt]

In creeker69's defense, the obvious formula to try is this:
=SUMPRODUCT((STORE="BLUE")*(NEWUSED="USED")*(NAME=C32)*(INDEX(WARR,,,1)<>"" + INDEX(WARR,,,2)<>"")*(MONTH=$C$25))

At least, I thought that was the first thing to try. But I got #VALUE! as its result.

It took me a while to figure out that the best fix was adding two pairs of parentheses. That creeker69 couldn't figure it out doesn't surprise me--you have to understand intimately how the formula works before you'd ever guess to try adding parentheses.

My rationalization is that putting each Boolean expression inside its own parentheses makes Excel respond by performing an arithmetic operation to combine their results, rather than a Boolean one.
INDEX(WARR,,,1)<>"" + INDEX(WARR,,,2)<>"" Boolean expression resulting in #VALUE! error
(INDEX(WARR,,,1)<>"")+(INDEX(WARR,,,2)<>"") Each Boolean expression is coerced (converted into a vector of 1 or 0) and the results added

Brad

 

[SkipVought]

There was no evidence of an attempt other than what had previously failed to work.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[byundt]

Taking his comment at face value, I think he did try to apply the INDEX trick towards counting:

Trying to use the same logic, I get a #VALUE error when I now try to count cells that are not empty.

 

[creeker69]

Brad,

Thank you so very much for all the help. Yea, despite what skip implied, I tried many combinations and got the #value.

It was as you said, the proper placement of the parentheses. Also, you saved me so much work by showing me how to use two non-contiguous columns in a name range. I was able to do a global replace in the formulas.

Brad, Once again, my sincere thanks!

Creeker69