Conditional Sums with Named Ranges

Kbroccardo

New Member
Joined
Feb 19, 2004
Messages
11
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Kbroccardo said:
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

New Member
Joined
Feb 19, 2004
Messages
11

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Kbroccardo said:
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

New Member
Joined
Feb 19, 2004
Messages
11

ADVERTISEMENT

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

New Member
Joined
Feb 19, 2004
Messages
11
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,102
Messages
5,768,104
Members
425,454
Latest member
khoro

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
Top