Excel 2003, which formula will count the cells that meet 2 conditi

[Guest]

I would like to use a formula like COUNTIF or SUMPRODUCT to test two ranges.
I want the formula to return the value of the number of cells that meet the
two conditions. Both columns contain TEXT, not numbers.

Like this:
=COUNTIF(--(Sheet1!B:B, "Move-In"),--(Sheet1!E:E,"A1"))

So if there are 10 Move-Ins with Type A1, then the value 10 would be
calulated.

 

[Guest]

Hi

If all the data is text, what do you mean by 'value of the number of
cells'?
Try this:
=SUMPRODUCT(--(Sheet1!B1:B1000="Move-In"),--(Sheet1!E1:E1000 = "A1"))
You cannot use full column ranges with SUMPRODUCT, and the ranges must be
the same size.

Andy.

 

[Guest]

Thanks Andy. If I have 10 new residents or "Move-in"s and they all moved
into an "A1" unit type, then my formula should count them and return the
number 10.

I revised the formula and it returns zero. I just read in my Excel book
that SUMPRODUCT treats nonnumeric entries as zero. I wonder if I should go a
differnt route and use LOOKUP.

 

[Mikeopolo]

I think the sumproduct formula posted above should have read:

=SUMPRODUCT(--(Sheet1!B1:B1000="Move-In")*--(Sheet1!E1:E1000 = "A1"))

Rgds
Mike

 

[Guest]

I don't see anything wrong with the proferred formula. You might want to
recheck your data. For instance, for a test, I entered some dummy
information in columns A and B and used the following formula and got the
expected results:

=SUMPRODUCT(--(A20:A31 = "Move-in"), --(B20:B31 = "A1"))

Assuming that your data is where you indicated and there are no hidden
spaces or funny characters, the formula should work. Try moving the parts of
the formula you are checking for and making sure it truly matches with what
is in your lookup range. In other words, copy the string Move-in directly
from your formula into a cell and trying a formula like, if you copied
Move-in to A1, =A1 = B25 (if B25 contains that string.) If it doesn't return
true, check for trailing spaces or maybe you used -- rather than -. It
probably isn't a formatting issue in this case, but if you were looking for a
number and it was formatted as text, your lookup would almost certainly fail.