Conditional Count part 2

[Ian Westwell]

I recently posted a query to count the frequency that "x"
was the value in column A and "y" was the value in
column "B". the answer quickly came back to use
the "Sumproduct" function eg
=SUMPRODUCT((data!B2:B2500="x")*(data!E2:E2500="y"))
The question now is how to extend that to a three way
condition to include "z" in column C, or even further??

Can anyone help?

 

[Bob Phillips]

Ian,

=SUMPRODUCT((data!B2:B2500="x")*(data!E2:E2500="y")*(data!C2:C2500="z"))

etc.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

cheers Bob it works!!

 

[Bob Phillips]

Ian,

Glad to hear it.

The formula as shown just counts the items, if you want to sum say column D
where the other tests apply, it is simply

=SUMPRODUCT((data!B2:B2500="x")*(data!E2:E2500="y"),(data!D22500))

It can be extended as previously described.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Ian,

The mail you sent to me directly ... I tried replying but it got bounced,
invalid domain. The mail I tried was (e-mail address removed).

Can you give me a valid email address?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Ian,

That was a typo in my post, I am still failing to get to you at
(e-mail address removed).

As I know you are reading this thread, I will post here ....


Westers,

It's not difficult, just another 2 tests of the same kind.

=SUMPRODUCT((data!B2:B12>=DATEVALUE("01/04/2003"))*((data!B2:B12<=DATEVALUE(
"30/04/2003"))*(data!E2:E12="y")*(data!C2:C12="z")))

Could I suggest that you read this previous post from Ken Wright on how
SUMPRODUCT works, it may help your understanding.

http://tinyurl.com/v85r

Also, if you want any more help, please email this account at
(e-mail address removed) as this tiscali account gets a lot of spam and I
have an aggressive spam filter on it. I only just noticed your email before
I deleted the lot, so the other account is safer and surer.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)