Prev: how to key in a1 information on e1 wil appear at b1
Next: Basic Question on autocomplete function
From: PhilC on 11 Apr 2010 23:54
hi...I'm looking for a function that will count frequency using multiple
variables in an unsorted data set using two variables. I can get the
information that I need by sorting the data and then using Data->Autofilter
and using the countif function ti isolate the variables individual. However,
this is problematic if the data gets re-sorted.

Data example

A B C
XA GM 125
XC BA 34
XA GM 23
XC NY 19
XA GM (blank)

I'm looking to count the number of occurrences of XA where the value of GM
is greater than zero, skipping blank entries. I've tried a number of
different functions but haven't had any luck so far.

any help would be appreciated.

thank you

phil

From: ozgrid.com on 12 Apr 2010 00:15
Do you mean?
=SUMPRODUCT((A1:A20="XA")*(B1:B20="GA")*(C1:C20>0))



--
Regards
Dave Hawley
www.ozgrid.com
"PhilC" <PhilC(a)discussions.microsoft.com> wrote in message
news:8468856B-1E29-4025-B655-507437C0286C(a)microsoft.com...
> hi...I'm looking for a function that will count frequency using multiple
> variables in an unsorted data set using two variables. I can get the
> information that I need by sorting the data and then using
> Data->Autofilter
> and using the countif function ti isolate the variables individual.
> However,
> this is problematic if the data gets re-sorted.
>
> Data example
>
> A B C
> XA GM 125
> XC BA 34
> XA GM 23
> XC NY 19
> XA GM (blank)
>
> I'm looking to count the number of occurrences of XA where the value of GM
> is greater than zero, skipping blank entries. I've tried a number of
> different functions but haven't had any luck so far.
>
> any help would be appreciated.
>
> thank you
>
> phil
>

From: PhilC on 12 Apr 2010 01:31
Dave,
thank you, yes that did the trick...I had the sumproduct function wrong, I
was using "=XA" not ="XA" - an important difference.


The solution you provided works provided I subtract for blank spaces. I have
to treat 0 and blank cells separately ( 0 is a possible outcome whereis blank
is no data available).

Thanks very much

Phil
 | 
Pages: 1
Prev: how to key in a1 information on e1 wil appear at b1
Next: Basic Question on autocomplete function