SUMIFS fomula

[Glaswegian]

Hi

I'm having a problem with a formula and could do with some help.

I need to sum data in column F + data in column G, subject to specific criteria in Column D.

The ranges in Columns F & G are named Debits and Debits3 respectively. These are dynamic ranges created using the following formula

=OFFSET(Data!$G$1,0,0,MATCH(9.99999999999999E+307,Data!$G:$G),1)

=OFFSET(Data!$F$1,0,0,MATCH(9.99999999999999E+307,Data!$F:$F),1)

The criteria for column D is simply "<820000".

My formula is

=SUMIFS(Debits:Debits3,Bank,"<820000")

but this produces a #VALUE! error.

I'm not sure where I'm going wrong and would appreciate some guidance.

Thanks.

[Bob Phillips]

It is probably different size ranges Iain. Try defining all of the counts for the dynamic ranges from the same source. I also found problems using the OFFSET formula so I used

=Data!$G$1:INDEX(Data!$G:$G,MATCH(9.99999999999999E+307,Data!$G:$G))

and

=Data!$F$1:INDEX(Data!$F:$F,MATCH(9.99999999999999E+307,Data!$G:$G))

for Debits and Debits3.

And finally, you can't string the ranges together, so do it as 2 SUMIFS

=SUMIFS(Debits,Bank,"<820000")+SUMIFS(Debits3,Bank,"<820000")

[Glaswegian]

Hi xld

Yes, when I step through the formula it's the 2 named ranges that show as the VALUE errors. I tried changing the OFFSET formula to your INDEX but I still get the VALUE error. I should have realised you could not add the ranges within the single formula!

I can do this in parts (all calculations etc are done on a hidden sheet anyway) so I'm not overly worried that it does not work.

Thanks again xld.

[Bob Phillips]

Did you change the Bank name definition as well?

[Glaswegian]

I have now!

However it looks like the Bank data may now be the issue - when evaluating the formula result that's the one that results in the VALUE error.

Don't waste any more time one this - I can get around it.

Appreciate your efforts, as always.

[Bob Phillips]

You can't say don't waste any more time Iain, I will fret all night

Can you post the workbook so I can see what the cause is?

[Glaswegian]

Lol - sample now attached. The calculations are on the Totals sheet - the Data sheet contains the Named Ranges. I've removed the user forms and some sheets (mainly used for storing other data) and there is only dummy data in the workbook.

Thanks again for your help.

[Bob Phillips]

As I suspected, it was different size ranges. If you change the names as below, where the last row is determined from column A for each rather than from the column being defined, it works fine.

Bank: =Data!$D$1:INDEX(Data!$D:$D,MATCH(9.99999999999999E+307,Data!$A:$A))

Debits: =Data!$D$1:INDEX(Data!$D:$D,MATCH(9.99999999999999E+307,Data!$A:$A))

Debites3: =Data!$D$1:INDEX(Data!$D:$D,MATCH(9.99999999999999E+307,Data!$A:$A))

[Glaswegian]

Hi xld

That is just brilliant!

Many thanks again.