And Operator

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have this array formula
{=IF(AND(A4,C4,E4="REG"),SUM(B4+D4+F4,0))}
However, if I change cell A4 to something other than "REG", I get the same formula result. I'm doing something wrong!

Thanks,
Howard
 
It's not an array formula and this is the way it should look

=IF(AND(A4="REG",C4="REG",E4="REG"),SUM(B4,D4,F4),0)

meaning: If all 3 cells contains "REG", then sum B4, D4, and E4, otherwise 0
--

Regards,

Peo Sjoblom


Howard said:
I have this array formula
{=IF(AND(A4,C4,E4="REG"),SUM(B4+D4+F4,0))}
However, if I change cell A4 to something other than "REG", I get the same
Click to expand...
formula result. I'm doing something wrong!
 
Hi Howard

1. I don't think you need an arry formula.
2. The AND formula in your example only evaluates cell E4.

Try the following
=IF(AND(A4="REG",C4="REG",E4="REG"),SUM(B4+D4+F4),0)
Frank
 
First off, I don't think your formula is doing what you think it is doing.

The AND function, if all those (A4, C4, E4) are supposed to be equal to "REG"
takes the following form:-

=AND(A4="REG",C4="REG",E4="REG")

BUT, that probably won't help you anyway, as Array formulas don't really like
AND and OR.

What is the formula trying to achieve? If ALL 3 equal REG you want to add
B4,D4,F4 or do you just want the contents of B4 if A4="REG" + D4 if C4 ="REG"
etc.

If the first, then you don't need the array formula, so try this:-

=IF(AND(A4="REG",C4="REG",E4="REG"),B4+D4+F4,0) entered normally, or with REG in
say A1
=IF(AND(A4=A1,C4=A1,E4=A1),B4+D4+F4,0)

or if the second, then try this:-

=(B4="REG")*C4+(D4="REG")*E4+(F4="REG")*G4

or this

=SUM(IF(ISERROR((B4:F4="REG")*(C4:G4)),0,(B4:F4="REG")*(C4:G4)))

array entered using CTRL+SHIFT+ENTER. Longer than the last but scales up better
if you include more columns.

If you are doing this for columns of data as opposed to the single row you
quoted, then take a look at the SUMPRODUCT function.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Howard said:
I have this array formula
{=IF(AND(A4,C4,E4="REG"),SUM(B4+D4+F4,0))}
However, if I change cell A4 to something other than "REG", I get the same
Click to expand...
formula result. I'm doing something wrong!
 
Thanks to both of you. I have a lot to learn
In a previous post relating to another formula, the writer referred to this as the sledgehammer method. What if I had a large number of cells that had "REG." Is there another way to do it? As I say, I'm trying to learn
Thanks
Howard
 
Define 'Large'. Do you mean across in terms of more columns than you listed, in
which case see my post for the array formula I gave you, or do you mean columns
of data in terms of 100 cells in Col A = REG give me data from the 100 cells in
Col B etc, in which case see my post for SUMPRODUCT. Clarify the question and
we can give you specifics. :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Howard said:
Thanks to both of you. I have a lot to learn!
In a previous post relating to another formula, the writer referred to this as
Click to expand...
the sledgehammer method. What if I had a large number of cells that had "REG."
Is there another way to do it? As I say, I'm trying to learn.
 
Hi Howard
one way would be to re-arrange your spreadsheet design. e.g.
- column A stores "REG" or blanks (or any other desired codes)
- column B stores the values

Now (for only one condition) you can use SUMIF
For more conditions you can use SUMPRODUCT

for SUMIF have a look at
http://www.mvps.org/dmcritchie/excel/sumif.htm

Frank
 
If ALL three cells (A4,C4,E4) contain "REG", then sum B4,D4,F4. If I had many cells that I wanted to test (A4,C4,E4 and so on out to AZ4), is there a way not to have to write A4="REG", C4="REG"......AZ4="REG"?
At this point, I'm just curious. This is not critical!
Thanks,
Howard
 
Sure, with the array formula I gave you. This assumes of course that the logic
holds good in that as per your example every numeric value is in a similar
offset position to the cell containing REG being evaluated, ie all your numbers
were consistent in being one cell to the right of the cell with REG in. In that
scenario you could just extend the formula

=SUM(IF(ISERROR((B4:F4="REG")*(C4:G4)),0,(B4:F4="REG")*(C4:G4)))

to

=SUM(IF(ISERROR((B4:AY4="REG")*(C4:AZ4)),0,(B4:AY4="REG")*(C4:AZ4)))

The ranges must be the same length, and although in this case they are offset by
one column, they are still the same length. It efefectively sets up a series of
TRUE/FALSE statements for each cell in the range B4:AY4 depending on whether or
not each cell contains REG. This gives

B C D E F G
4 TRUE FALSE TRUE FALSE TRUE FALSE

and then multiplies that range by the same range one cell to the right (Excel
sees TRUE = 1, FALSE = 0)

so you get a series of TRUE*Number + TRUE*Number + TRUE*Number (Or FALSE if not
equal to REG)

which = 1*Number + 1*Number + 1*Number

which = Number + Number + Number etc

The reason for the range being offset by one column, is that Excel will multiply
the first value in the first range by the first value in the second range,
regardless of what columns they start in.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Howard said:
If ALL three cells (A4,C4,E4) contain "REG", then sum B4,D4,F4. If I had many
Click to expand...
cells that I wanted to test (A4,C4,E4 and so on out to AZ4), is there a way not
to have to write A4="REG", C4="REG"......AZ4="REG"?
 
Back
Top