If and formula

Hi everyone,

I need some help with a formula, the formula will look at the "U" column and "J" column and if it meets both conditions it will add a 1.
I need this formula to look at "J" column and meet the condition not only for Vancouver but for other cities as well, so that if J = Vancouver add 1, or Montreal add 1, etc.
can some one help?

=IF(AND(U:U="Notebook computer",J:J ="Vancouver",I:I="Deployed"),1,0)

Hi,

Not sure if I understand you completely but I think you want to count the number of times all the conditions are met.

If this is the case thaen you need to use SUMPRODUCT - note, you cannot use whole column ranges with this. Adjust the range I have used as required.

Please Login or Register  to view this content.

Let me know if this is what you want.

Ed

Originally Posted by EdMac

Hi,

Not sure if I understand you completely but I think you want to count the number of times all the conditions are met.

If this is the case thaen you need to use SUMPRODUCT - note, you cannot use whole column ranges with this. Adjust the range I have used as required.

Please Login or Register  to view this content.

Let me know if this is what you want.

Ed

Hi EdMac,

thank you for your reply,
the formula I am using is pasted on every cell in column "AT"
=IF(AND(U:U="Notebook computer",J:J ="Vancouver",I:I="Deployed"),1,0)

if it meets the 3 conditions it will place a "1" in column AT, at the end of this column I sum all the results.
what I would like to do is make column "J" variable, so that it will place a "1"
on column "AT" whether it is Vancouver, Montreal or Ottawa (there are other cities as well which need not be counted.
I tried the formula you suggested but I was not sure where to place it.

All your help is much appreciated.

Hi,

can you attach a zipped copy of your workbook?

The formula I gave you can be placed in any cell and will give you a total of the number of times that the condition is met. Rather than evaluating each row and then summing the total of the rows, it does it all in one calculation.

If you want to do it the way you have said, each formula in column AT will need to have the row number included in it e.g for row 1

Please Login or Register  to view this content.

Ed