Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array problems
Hello all,
I have the following formula using 3 name arrays of Day,City,columL. ColumL is a sum of L11:L19. I am receiving the #Value error. I've tried every way I know to solve this. Any ideas?? =SUM(AND(Day,"=Sunday",City,"=West",columL)). The below formula works but I can't seem to get the second condition to work. =SUMIF(Day,"=Sunday",columL) Thanks to all who take the time to answer!!!!! BD |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array problems
For multiple criteria you cannot use the SUMIF() function.
Presuming that your named ranges are all the same shape you can use the SUMPRODUCT()function as follows: =SUMPRODUCT(1*(Day="Sunday"),1*(City="West"),ColmL ) Alternately, you could use an array formula like the following: {=SUM(IF((Day="Sunday")*(City="West"),ColumL))} which must be entered using a Ctrl^Shift^Enter key combination. This creates the bracket characters which are not entered by you HTH, TK "Bruce D." wrote: Hello all, I have the following formula using 3 name arrays of Day,City,columL. ColumL is a sum of L11:L19. I am receiving the #Value error. I've tried every way I know to solve this. Any ideas?? =SUM(AND(Day,"=Sunday",City,"=West",columL)). The below formula works but I can't seem to get the second condition to work. =SUMIF(Day,"=Sunday",columL) Thanks to all who take the time to answer!!!!! BD |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array problems
TK,
Tks for your reply but what if I want a "OR" condition. If the day=sunday or city = west sum the values in colum L. -- Bruce DiPaola "T Kirtley" wrote: For multiple criteria you cannot use the SUMIF() function. Presuming that your named ranges are all the same shape you can use the SUMPRODUCT()function as follows: =SUMPRODUCT(1*(Day="Sunday"),1*(City="West"),ColmL ) Alternately, you could use an array formula like the following: {=SUM(IF((Day="Sunday")*(City="West"),ColumL))} which must be entered using a Ctrl^Shift^Enter key combination. This creates the bracket characters which are not entered by you HTH, TK "Bruce D." wrote: Hello all, I have the following formula using 3 name arrays of Day,City,columL. ColumL is a sum of L11:L19. I am receiving the #Value error. I've tried every way I know to solve this. Any ideas?? =SUM(AND(Day,"=Sunday",City,"=West",columL)). The below formula works but I can't seem to get the second condition to work. =SUMIF(Day,"=Sunday",columL) Thanks to all who take the time to answer!!!!! BD |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array problems
Also, it is not summing correctly. One record has both sunday and west for a
total of 24 and another record has only sunday for a total of 22. The formula does not add the second record for some reason. TK, Tks for your reply but what if I want a "OR" condition. If the day=sunday or city = west sum the values in colum L. -- Bruce DiPaola "T Kirtley" wrote: For multiple criteria you cannot use the SUMIF() function. Presuming that your named ranges are all the same shape you can use the SUMPRODUCT()function as follows: =SUMPRODUCT(1*(Day="Sunday"),1*(City="West"),ColmL ) Alternately, you could use an array formula like the following: {=SUM(IF((Day="Sunday")*(City="West"),ColumL))} which must be entered using a Ctrl^Shift^Enter key combination. This creates the bracket characters which are not entered by you HTH, TK "Bruce D." wrote: Hello all, I have the following formula using 3 name arrays of Day,City,columL. ColumL is a sum of L11:L19. I am receiving the #Value error. I've tried every way I know to solve this. Any ideas?? =SUM(AND(Day,"=Sunday",City,"=West",columL)). The below formula works but I can't seem to get the second condition to work. =SUMIF(Day,"=Sunday",columL) Thanks to all who take the time to answer!!!!! BD |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array problems
With the Array approach its pretty simple; you just use the + operator in
place of the * character to indicate an OR condition like the following: {=SUM(IF((Day="Sunday")+(City="West"),ColumL))} This returns the total of all rows in ColumL with a Day of "Sunday" OR a City of "West" An OR condition is more complicated with the SUMPRODUCT() but can be done by combining multiple formulas such as: =SUMIF(Day,"Sunday",ColumL)+SUMIF(City,"West",Colu mL)-SUMPRODUCT(1*(Day="Sunday"),1*(City="West"),ColumL ) This uses Sumif() functions to add all rows with either Day="Sunday" or City="West" and uses the SUMPRODUCT() function to eliminate double counting of rows that are both Day="Sunday" and City="West" TK "Bruce D." wrote: TK, Tks for your reply but what if I want a "OR" condition. If the day=sunday or city = west sum the values in colum L. -- Bruce DiPaola "T Kirtley" wrote: For multiple criteria you cannot use the SUMIF() function. Presuming that your named ranges are all the same shape you can use the SUMPRODUCT()function as follows: =SUMPRODUCT(1*(Day="Sunday"),1*(City="West"),ColmL ) Alternately, you could use an array formula like the following: {=SUM(IF((Day="Sunday")*(City="West"),ColumL))} which must be entered using a Ctrl^Shift^Enter key combination. This creates the bracket characters which are not entered by you HTH, TK "Bruce D." wrote: Hello all, I have the following formula using 3 name arrays of Day,City,columL. ColumL is a sum of L11:L19. I am receiving the #Value error. I've tried every way I know to solve this. Any ideas?? =SUM(AND(Day,"=Sunday",City,"=West",columL)). The below formula works but I can't seem to get the second condition to work. =SUMIF(Day,"=Sunday",columL) Thanks to all who take the time to answer!!!!! BD |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
defintion of array function | Excel Discussion (Misc queries) | |||
How to multiply all cells in array by factor | Excel Discussion (Misc queries) | |||
Having some problems linking an array across excel files | Excel Discussion (Misc queries) | |||
Transpose words and numbers into array of different proportions | Excel Discussion (Misc queries) | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |