Conditional Sum using Dynamic Ranges

[headtoadie]

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

 

[shades]

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)

 

[Aladin Akyurek]

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

 

[headtoadie]

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,