Conditional Sums with Named Ranges

Kbroccardo

New Member
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.

MrExcel MVP
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

n/a

MrExcel MVP
Kbroccardo said:

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

Kbroccardo

New Member

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?

MrExcel MVP
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

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
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

New Member
Never mind. I figured it out.

Thanks for all of your help.

Karen

Replies
2
Views
249
Replies
10
Views
255
Replies
3
Views
410
Replies
1
Views
83
Replies
12
Views
1K

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.

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.

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

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