Conditional Count part 2

  • Thread starter Thread starter Ian Westwell
  • Start date Start date
I

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?
 
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)
 
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!D2:D2500))

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)
 
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)
 
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)
 
Back
Top