="Z" dual use?

[Scott Calkins via OfficeKB.com]

I am using the following 2 formulas to pull occurrances of a letter in a
column.
{=COUNT(IF(('Raw data'!L1:L65500<>"A")*('Raw data'!B1:B65500="L"),'Raw
data'!Q1:Q65500))}
and
{=COUNT(IF(('Raw data'!L1:L65500<>"A")*('Raw data'!B1:B65500="Z"),'Raw
data'!Q1:Q65500))}

The first formula works fine, but the second comes back with the wrong
total(should be 20 with my test data, giving 230). The only difference is
changing the "L" to a "Z". Is ="Z" used for something else in excel, i.e.
z-test or something? If so is there another way to get the occurrances of
"Z" in column B when column L ="A"?

 

[Bob Phillips]

I cut the range down in my test, but worked fine. Sure you don't have 230
(in 65500 it may be hard to find).

 

[Scott Calkins via OfficeKB.com]

Yep, the range may be 1-65500 but only 1-262 have data in them and of those
"Z" only is used 20 times.

 

[Sandy Mann]

Scott,

I don't have an answer to your question but when I test your formula with
Column L empty and Column B with several Z's then, like Bob, I get a
(correct) count of the number of Z's in Column B provided that Column Q is
empty or has numbers. If Column Q has letters then the count decreases by 1
for every letter in Q in the same row as a Z in Column B. It may be
therefore that a re-write of your formula may be in order. What data is
contained in Column Q? If it is just Z'd with no corresponding A in Column
L you want then

{=COUNT(IF(('Raw data'!L1:L65500<>"A")*('Raw data'!B1:B65500="Z"),))}

works just as well for me. Try reducing the range sizes and then
highlighting everything in your formula except the COUNT and press F9 to see
what is actually being counted.


Regards

Sandy

 

[Guest]

try selecting everything
<data><filter><auto filter>
Select custom not equal to "A" in column L and "Z" in column B .
Check column Q what is there.

This should let you see if there is something odd in your file.
try also
=sumproduct(--('Raw data'!L1:L65500<>"A"),--('Raw
data'!B1:B65500="Z"),--(isnumber('Raw data'!B1:B65500))

 

[Guest]

one additional comment your equations show when L <> A but your Question is
for when column L = A. change my trys to =A ranter than <> A if you really
do want to have the =