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

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

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

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

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