# Conditional Sum using Dynamic Ranges

##### Board Regular
All, I'm trying to use dynamic ranges for a conditional sum statement, but it's not working - just returns #N/A. If I use static ranges the conditional sum statement works fine. The conditional sum statement looks like below and is entered as an array:

{=SUM(IF(Major=F5,IF(Type=G5,Amount,0),0))}

I'm creating the dynamic ranges using the Insert->Name function on the menu. The formula looks correct. After I enter the offset formula if I click the little box at the end of the window where you enter the formula it highlights the correct range.

The offset statement creates a range named "Major" and looks like:

=OFFSET(sheet1!\$A\$1,0,1,COUNTA(sheet1!\$B:\$B),1)

So, independently the two formulas appear correct, but when I try to use them in unison it breaks.

Is it possible to use dynamic ranges with conditional sum statements? If so, what am I doing wrong?

Thanks,
HT

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
The offset statement creates a range named "Major" and looks like:

=OFFSET(sheet1!\$A\$1,0,1,COUNTA(sheet1!\$B:\$B),1)

Shouldn't this formula be:

=OFFSET(sheet1!\$A\$1,0,1,COUNTA(sheet1!\$A:\$A),1)

All, I'm trying to use dynamic ranges for a conditional sum statement, but it's not working - just returns #N/A. If I use static ranges the conditional sum statement works fine. The conditional sum statement looks like below and is entered as an array:

{=SUM(IF(Major=F5,IF(Type=G5,Amount,0),0))}

I'm creating the dynamic ranges using the Insert->Name function on the menu. The formula looks correct. After I enter the offset formula if I click the little box at the end of the window where you enter the formula it highlights the correct range.

The offset statement creates a range named "Major" and looks like:

=OFFSET(sheet1!\$A\$1,0,1,COUNTA(sheet1!\$B:\$B),1)

So, independently the two formulas appear correct, but when I try to use them in unison it breaks.

Is it possible to use dynamic ranges with conditional sum statements? If so, what am I doing wrong?

Thanks,
HT

What kind of data do you have in A (numbers,dates,text), B, and the Amount column (which column) ?

Got it solved, the static ranges where a different size than the dynamic range. Made them all dynamic (and therefore that same size) now it works fine.

Thanks for the help
HT

Shades: in the offset command the CountA function only defines how many rows to use, so it needs to point to a column that has no blank cells - it can be any column. The actual column for the range is designated by the 1st and 3rd statements sheet1!\$A\$1,0,1,

Replies
4
Views
188
Replies
0
Views
269
Replies
1
Views
137
Replies
1
Views
83
Replies
4
Views
420

1,203,082
Messages
6,053,422
Members
444,662
Latest member
AaronPMH

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

### Which adblocker are you using?

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