I have an issue with conditional sums, my data is G/L codes which I have
separated into cost centre element and account code element, e.g. 550 (cost
centre) & 2000 (account code).

I have used the SUMIF to add up account classes, e.g. accounts in the range
2000 - 2999, etc. by using wild cards such as "2???". The easiest examples
of which a
=SUMIF(output!$B$125:$B$819,"2???",output!L$125:L$ 819) - Whole Co.
=SUMIF(output!$C$125:$C$819,"550 2???",output!P$125:P$819) - Cost Centre

To check my class totals back to the control total I have used SUMIF on the
cost centre. So far, so good.

However, I need to add multiple cost centres together as a department (12 in
one case) and I am getting issues with the formula being too long (using
multiples of e.g.2 above) and so have tried to use an array formula instead.
I have tried separating the codes so to look for 550 first and 2??? second:
{=SUM(IF((output!$A$125:$A$819="550")+(output!$A$1 25:$A$819="2???"),output!M$125:M$819))}
But the result is the cost centre total as it ignores the wild card part of
the formulae - running a smaller version with just the ...="2???" return nil,
which is not true and using specifics of 2000 returns a value.

Am I flogging a dead horse, or is it possible to get an array to do what I
want without breaching the number of characters in a cell constraint that the
long winded way runs into?

Rgds,

Nigel

Could you show us some sample data.


--
Thanks,
Shane Devenshire


"ndrinkwater" wrote:

I have an issue with conditional sums, my data is G/L codes which I have
separated into cost centre element and account code element, e.g. 550 (cost
centre) & 2000 (account code).

I have used the SUMIF to add up account classes, e.g. accounts in the range
2000 - 2999, etc. by using wild cards such as "2???". The easiest examples
of which a
=SUMIF(output!$B$125:$B$819,"2???",output!L$125:L$ 819) - Whole Co.
=SUMIF(output!$C$125:$C$819,"550 2???",output!P$125:P$819) - Cost Centre

To check my class totals back to the control total I have used SUMIF on the
cost centre. So far, so good.

However, I need to add multiple cost centres together as a department (12 in
one case) and I am getting issues with the formula being too long (using
multiples of e.g.2 above) and so have tried to use an array formula instead.
I have tried separating the codes so to look for 550 first and 2??? second:
{=SUM(IF((output!$A$125:$A$819="550")+(output!$A$1 25:$A$819="2???"),output!M$125:M$819))}
But the result is the cost centre total as it ignores the wild card part of
the formulae - running a smaller version with just the ...="2???" return nil,
which is not true and using specifics of 2000 returns a value.

Am I flogging a dead horse, or is it possible to get an array to do what I
want without breaching the number of characters in a cell constraint that the
long winded way runs into?

Rgds,

Nigel