software

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, quot;Move-Inquot;),--(Sheet1!E:E,quot;A1quot;))

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

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

Andy.

quot;lawoman35quot; gt; wrote in message
...
gt;I would like to use a formula like COUNTIF or SUMPRODUCT to test two
gt;ranges.
gt; I want the formula to return the value of the number of cells that meet
gt; the
gt; two conditions. Both columns contain TEXT, not numbers.
gt;
gt; Like this:
gt; =COUNTIF(--(Sheet1!B:B, quot;Move-Inquot;),--(Sheet1!E:E,quot;A1quot;))
gt;
gt; So if there are 10 Move-Ins with Type A1, then the value 10 would be
gt; calulated.
gt;
Thanks Andy. If I have 10 new residents or quot;Move-inquot;s and they all moved
into an quot;A1quot; 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.

quot;Andyquot; wrote:

gt; Hi
gt;
gt; If all the data is text, what do you mean by 'value of the number of
gt; cells'?
gt; Try this:
gt; =SUMPRODUCT(--(Sheet1!B1:B1000=quot;Move-Inquot;),--(Sheet1!E1:E1000 = quot;A1quot;))
gt; You cannot use full column ranges with SUMPRODUCT, and the ranges must be
gt; the same size.
gt;
gt; Andy.
gt;
gt; quot;lawoman35quot; gt; wrote in message
gt; ...
gt; gt;I would like to use a formula like COUNTIF or SUMPRODUCT to test two
gt; gt;ranges.
gt; gt; I want the formula to return the value of the number of cells that meet
gt; gt; the
gt; gt; two conditions. Both columns contain TEXT, not numbers.
gt; gt;
gt; gt; Like this:
gt; gt; =COUNTIF(--(Sheet1!B:B, quot;Move-Inquot;),--(Sheet1!E:E,quot;A1quot;))
gt; gt;
gt; gt; So if there are 10 Move-Ins with Type A1, then the value 10 would be
gt; gt; calulated.
gt; gt;
gt;
gt;
gt;


I think the sumproduct formula posted above should have read:

=SUMPRODUCT(--(Sheet1!B1:B1000=quot;Move-Inquot;)*--(Sheet1!E1:E1000 = quot;A1quot;))

Rgds
Mike--
Mikeopolo
------------------------------------------------------------------------
Mikeopolo's Profile: www.excelforum.com/member.php...oamp;userid=18570
View this thread: www.excelforum.com/showthread...hreadid=525709I 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 = quot;Move-inquot;), --(B20:B31 = quot;A1quot;))

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.
--
Kevin Vaughnquot;lawoman35quot; wrote:

gt; Thanks Andy. If I have 10 new residents or quot;Move-inquot;s and they all moved
gt; into an quot;A1quot; unit type, then my formula should count them and return the
gt; number 10.
gt;
gt; I revised the formula and it returns zero. I just read in my Excel book
gt; that SUMPRODUCT treats nonnumeric entries as zero. I wonder if I should go a
gt; differnt route and use LOOKUP.
gt;
gt; quot;Andyquot; wrote:
gt;
gt; gt; Hi
gt; gt;
gt; gt; If all the data is text, what do you mean by 'value of the number of
gt; gt; cells'?
gt; gt; Try this:
gt; gt; =SUMPRODUCT(--(Sheet1!B1:B1000=quot;Move-Inquot;),--(Sheet1!E1:E1000 = quot;A1quot;))
gt; gt; You cannot use full column ranges with SUMPRODUCT, and the ranges must be
gt; gt; the same size.
gt; gt;
gt; gt; Andy.
gt; gt;
gt; gt; quot;lawoman35quot; gt; wrote in message
gt; gt; ...
gt; gt; gt;I would like to use a formula like COUNTIF or SUMPRODUCT to test two
gt; gt; gt;ranges.
gt; gt; gt; I want the formula to return the value of the number of cells that meet
gt; gt; gt; the
gt; gt; gt; two conditions. Both columns contain TEXT, not numbers.
gt; gt; gt;
gt; gt; gt; Like this:
gt; gt; gt; =COUNTIF(--(Sheet1!B:B, quot;Move-Inquot;),--(Sheet1!E:E,quot;A1quot;))
gt; gt; gt;
gt; gt; gt; So if there are 10 Move-Ins with Type A1, then the value 10 would be
gt; gt; gt; calulated.
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;