Conditional Sums with Named Ranges

[Kbroccardo]

I'm trying to create a CSE using named ranges but I keep getting errors. I'm either inputing the data incorrectly or it just doesn't work.

Here's a sample of my current formula: =SUM(IF($A$30:$A$206="DBarnes",IF($F$30:$F$206="Forecast",IF($I$30:$I$206>=DATEVALUE("1/1/2004"),IF($I$30:$I$206<=DATEVALUE("1/31/2004"),$H$30:$H$206,0),0),0),0))

I would like to change the cell references to named ranges. As an example I would like the range $a$30:$a$206 to be named primary.

Can this be done and still use the conditions I have already set up?

Thanks!
Karen

 

[Aladin Akyurek]

I'm trying to create a CSE using named ranges but I keep getting errors. I'm either inputing the data incorrectly or it just doesn't work...

What error do you get?

 

[Kbroccardo]

n/a

 

[Aladin Akyurek]

n/a

Does any of the relevant ranges house #N/A as a result of a formula or otherwise?

 

[Kbroccardo]

No. I can't figure out why this is happening. I have checked with another collegue and neither of us can see where there is an error in the formula.

There is enough data in the set to capture a result based on the formula. I just may have to revert back to the original formula to get the results I need.

Any thoughts?

 

[Aladin Akyurek]

No. I can't figure out why this is happening. I have checked with another collegue and neither of us can see where there is an error in the formula.

There is enough data in the set to capture a result based on the formula. I just may have to revert back to the original formula to get the results I need.

Any thoughts?

=SUMPRODUCT(--($A$30:$A$206="DBarnes"),--($F$30:$F$206="Forecast"),--(TEXT($I$30:$I$206,"m/yy")=TEXT(DATE(2004,1,1),"m/yy")),$H$30:$H$206)

Do you still get #N/A?

 

[Kbroccardo]

I cut your formula and pasted it into mine and it worked. However, perhaps its my stupidity, it didn't work for the other months.

I do a quarterly update by months. The formula worked for January, but didn't for February, or March.

I thought I had changed the formula correctly to reflect those months. I'm not an expert by any means so it could be my error.

 

[Kbroccardo]

Just tried it again, and it was my error. It did work in all the fields. Now my next question, why did it change the format of the cell? It actually took over 2 cells worth of infomation.

 

[Kbroccardo]

Never mind. I figured it out.

Thanks for all of your help.

Karen