Dynamic range, SUMIFS, and blank spaces

[arows]

Hi,

I have a list of names in one column, and how much they spent on the second. On the side I am using this formula to calculate how much each person has spent:
"=SUMIFS(Spent,Name,L7,Spent,">0")" where Spent is the dynamic range of the names column, spent the dynamic range for how much they spent, L7 the cell containing the name, and if they spent more than 0.

If I add names+amount spent, everything works fine and it calculates the new total. However, if I leave a few cells blank and then input the same thing (name, $), it won't count it. I don't get an error at all, it just doesn't count it.

Any idea on how to solve this?

 

[kennedy_clan]

Would guess your Dynamic Range is wrong, for example is Names are in column L and starts at L3 then use the following as your "Name" Named Range:

=OFFSET(Sheet1!$L$3,0,0,SUMPRODUCT(MAX((Sheet1!$L:$L<>"")*(ROW(Sheet1!$L:$L))))-2,1)

Then do same for Spent (changing column letter to spent column ref).

 

[XOR LX]

=OFFSET(Sheet1!$L$3,0,0,SUMPRODUCT(MAX((Sheet1!$L:$L<>"")*(ROW(Sheet1!$L:$L))))-2,1)

This construction will have to calculate over more than a million cells, by virtue of it using entire column references. What's more, OFFSET is fully volatile.

Preferable is either to use an upper bound on the range references, e.g. 1000, or, even better, either:

=Sheet1!$L$3:INDEX(Sheet1!$L:$L,MATCH(REPT("z",255),Sheet1!$L:$L))

if column L contains text values only;

=Sheet1!$L$3:INDEX(Sheet1!$L:$L,MATCH(9.99999999999999E+307,Sheet1!$L:$L))

if column L contains numerical values only, and:

=Sheet1!$L$3:INDEX(Sheet1!$L:$L,MAX(MATCH(9.99999999999999E+307,Sheet1!$L:$L),MATCH(REPT("z",255),Sheet1!$L:$L)))

if it can contain both datatypes.

This construction with INDEX is only partially volatile. What's more, this set-up is not necessarily forced to calculate over all one million-plus rows, and so will be much less heavy in terms of resource required.

Regards