PDA

View Full Version : [SOLVED] SUMIFS fomula



Glaswegian
01-27-2015, 03:55 AM
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
01-27-2015, 04:54 AM
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
01-27-2015, 05:40 AM
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
01-27-2015, 08:09 AM
Did you change the Bank name definition as well?

Glaswegian
01-27-2015, 09:14 AM
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
01-27-2015, 10:30 AM
You can't say don't waste any more time Iain, I will fret all night:crying:

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

Glaswegian
01-28-2015, 02:31 AM
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
01-28-2015, 06:59 AM
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
01-28-2015, 07:18 AM
Hi xld

That is just brilliant!

Many thanks again.