Prev: cant pass array to excel from c++ using xloper structure
Next: IF statement
From: MarkN on 4 Nov 2009 22:41
Hello,

I'm struggling with what I think requires a sumproduct function but I can't
make it work and I'm going cross eyed...

I need to count the number of occurences of "x" in column c if the value in
column a = "text1" or "text2" but column b <> "text3".


--
Any help appreciated,
MarkN
From: Jacob Skaria on 4 Nov 2009 23:23
Hi Mark

Try the below
=SUMPRODUCT((ISNUMBER(MATCH(A1:A10,{"text1","text2"},0)))*(B1:B10<>"text3")*(C1:C10="x"))

If this post helps click Yes
---------------
Jacob Skaria


"MarkN" wrote:

> Hello,
>
> I'm struggling with what I think requires a sumproduct function but I can't
> make it work and I'm going cross eyed...
>
> I need to count the number of occurences of "x" in column c if the value in
> column a = "text1" or "text2" but column b <> "text3".
>
>
> --
> Any help appreciated,
> MarkN
From: T. Valko on 4 Nov 2009 23:32
Try this...

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A20,{"text1","text2"},0))),--(B2:B20<>"text3"),--(C2:C20="x"))

--
Biff
Microsoft Excel MVP


"MarkN" <MarkN(a)discussions.microsoft.com> wrote in message
news:DD4F1C32-0B46-4756-867D-0D6BF0B05D97(a)microsoft.com...
> Hello,
>
> I'm struggling with what I think requires a sumproduct function but I
> can't
> make it work and I'm going cross eyed...
>
> I need to count the number of occurences of "x" in column c if the value
> in
> column a = "text1" or "text2" but column b <> "text3".
>
>
> --
> Any help appreciated,
> MarkN


From: MarkN on 5 Nov 2009 01:30
Thanks very much Jacob,

Not only for the prompt reply but it works a treat.
--
Thanks,
MarkN


"Jacob Skaria" wrote:

> Hi Mark
>
> Try the below
> =SUMPRODUCT((ISNUMBER(MATCH(A1:A10,{"text1","text2"},0)))*(B1:B10<>"text3")*(C1:C10="x"))
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "MarkN" wrote:
>
> > Hello,
> >
> > I'm struggling with what I think requires a sumproduct function but I can't
> > make it work and I'm going cross eyed...
> >
> > I need to count the number of occurences of "x" in column c if the value in
> > column a = "text1" or "text2" but column b <> "text3".
> >
> >
> > --
> > Any help appreciated,
> > MarkN
 | 
Pages: 1
Prev: cant pass array to excel from c++ using xloper structure
Next: IF statement