Counting list entries on 2 criteria

[Ken McLennan]

G'day there One & All,

Having pulled out what little hair remained, I now turn to those
who know what they'e doing.

I have a list comprising about 8 columns. After importing data
from a text file it can have any number of rows. Once the data is nice &
comfy in my summary worksheet (with great imagination I named it
"SumSheet"), various formulae on my "Analysis" sheet are intended to
display selected statistics.

The data consists of sets of readings which fall into a critical
range, eg: .000/.019, .020/.049, .050/.079, etc. These readings are
listed in column 'K'. Column 'F' records details of the test in a
numeric code, 1 = Static, 2 = Mobile, 99 = Test, etc.

In the appropriate cells of my Analysis sheet I count how many of
each range, eg: =COUNTIF(SumSheet!$K:$K,".000/.019"), etc. These work
fine except that they also count the Test readings and a number of other
reading classes which are skewing my results. I need to count as the
above =COUNTIF, except including a Not Where the column F cells = "99".

I've tried various combinations of nested countif, just plain
"if", SumProduct, and a few others with no success. Is it possible to
have a formula preclude counting the cells in column K, if the
corresponding cell in column F = "99"?

I've also tried autofiltering to hide the $F:$F = "99" rows, but
COUNTIF counted the hidden cells also.

Hoping for help soonish,
See ya & thanks,
Ken McLennan
Qld, Australia.

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(SumSheet!$K:$K=".000/.019"),--(SumSheet!
$F:$F<>99))

 

[Frank Kabel]

Hi
sorry, make this:
=SUMPRODUCT(--(SumSheet!$K1:$K1000=".000/.019"),--
(SumSheet!$F1:$F1000<>99))

Reason: SUMPRODUCT can't handle ranges like K:K

 

[Ken McLennan]

G'day there Frank,

=SUMPRODUCT(--(SumSheet!$K1:$K1000=".000/.019"),--
(SumSheet!$F1:$F1000<>99))

Many, many lots of thankyou for that Frank.

If I may ask, what do the "--" mean. I seem to recall the
technique being mentioned here previously, but I don't remember what was
discussed.

Reason: SUMPRODUCT can't handle ranges like K:K

AHA!!!!

I thought it was just my ineptitude coming to the fore, but I see
it was just a complete lack of knowledge instead. That's *MUCH* more
comforting =)

See ya
(and thanks again)
Ken

 

[Ken McLennan]

G'day there again Frank,

If I may ask, what do the "--" mean. I seem to recall the
technique being mentioned here previously, but I don't remember what was
discussed.

Disregard this bit. I found the answer elsewhere in the NG.
Although I've no doubt you'd have explained if I hadn't, so thanks for
that too.

See ya
Ken

 

[Frank Kabel]

Hi
if you're still interested see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Ken McLennan]

G'day there Frank,

if you're still interested see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Yup. Went to see it just now while playing Jethro Tull's Aqualung
album in the background.

I surprised myself by understanding quite a bit of the
explanation. I doubt that I could explain it to anyone, but I was able
to follow the it and the logic didn't escape me. (I'm not real good with
Boolean. The concept is simple, but the implementation leaves me
behind).

Be that as it may, I certainly do now understand the SUMPRODUCT
function a lot more than I did while relying on the Help file.

I've also bookmarked that site =)

Thanks once more,
Ken