Conditional Sum problem
Thnaks for your help, I'd started to guess that was the case and have devised
a key for the groups so that I can sum these.
Rgds,
Nigel
"ShaneDevenshire" wrote:
Hi,
First, SUMIF supports wildcard, but SUM and IF do not.
{=SUM(IF((output!$A$125:$A$819="550")+(output!$A$1 25:$A$819="2???"),output!M$125:M$819))}
Consider replacing 2??? with two conditions
output!$A$125:$A$819=2000
output!$A$125:$A$819<3000
I'm not sure whether the 2xxx stuff is a numerical entry or text. if it is
text your formula is going to become more complicated, for example you may
need to do something like
VALUE(output!$A$125:$A$819)=2000)
or
--(output!$A$125:$A$819)=2000
--
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
|