Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Sum problem
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Sum problem
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Sum problem | Excel Worksheet Functions | |||
Conditional Sum problem | Excel Worksheet Functions | |||
Conditional Sum problem | Excel Worksheet Functions | |||
Conditional Sum Problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) |