COUNTIF (?) on two criteria

[Django Cat]

I have a spreadsheet that logs work activities and records team
members' completion of project units.

Column B records team member names. Column C has project unit status -
'pending', 'beta' or 'completed'.

It's easy to set up a formula to count how many units are allocated to
each team member - =COUNTIF(B1:B1000,"Joe"). But what I need to do is
set up formulas to count how many units Joe has completed, or has
pending or at beta, and this means assessing data in both columns -
which COUNTIF won't do.

This doesn't strike me as an especially uncommon thing to want to do,
and I'd guess it may be straightforward - any help from the assembled
Excel experts would be very gratefully received.

DC



--

 

[Nick Hodge]

Django Cat

How about

=SUMPRODUCT((A1:A17="Joe")*(B1:B17="Beta"))

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[Django Cat]

=SUMPRODUCT((A1:A17="Joe")*(B1:B17="Beta"))

Thanks, Nick. Yes, that works. If you've got a minute to post
explaining *how* it works I'd be really grateful - I've not used
SUMPRODUCT before (and I can't see how the * in the middle of the
formula is working).

Django

--

 

[Nick Hodge]

Django

All SUMPRODUCT does is take a series of arrays and multiply one element by
the same element in the next array and sums the result (product), however it
has lots of other neat uses. Best detailed here
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

In your case it is taking two arrays. A1:A17 and B1:B17. It also has a
criteria on each so each one is coerced into TRUE or FALSE (True = 1, False
= 0)

So if we are looking at the first array/criteria group, looking for "Joe"
you end up with 1 where the cell is Joe and 0 where it is not.

Now take the second array/criteria group, looking for "Beta", again we end
up with an array of 1's, where the cell is "Beta", 0's where it is not.

So, we now have two 'matching' arrays of 0's and 1's.

If we multiply each element of the array (your original question), we get a
series of...

1*1 = 1 (Where "Joe" is TRUE (1) AND "Beta" is TRUE (1))
1*0 = 0 (Where "Joe" is TRUE (1) AND "Beta" is FALSE (0))
0*1 = 0 (Where "Joe" is FALSE (0) AND "Beta" is TRUE (1))

SUMPRODUCT simply now takes these results 1,0,1,0,0,0,1,0 and SUMS them.
Effecting a count of the 1's.

That's probably as clear as mud! Read the web page will probably do more for
you. It can also be used for multiple SUMIF criteria and bear in mind,
Excel 2007 has a few multi-criteria ones build in

SUMIFS, COUNTIFS, AVERAGEIFS...

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[Django Cat]

From: "Nick Hodge" <[email protected]>
Subject: Re: COUNTIF (?) on two criteria
Date: Sun, 18 May 2008 11:15:24 +0100
Newsgroups: microsoft.public.excel

Django

All SUMPRODUCT does is take a series of arrays and multiply one
element by the same element in the next array and sums the result
(product), however it has lots of other neat uses. Best detailed here
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

In your case it is taking two arrays. A1:A17 and B1:B17. It also has
a criteria on each so each one is coerced into TRUE or FALSE (True =
1, False = 0)

So if we are looking at the first array/criteria group, looking for
"Joe" you end up with 1 where the cell is Joe and 0 where it is not.

Now take the second array/criteria group, looking for "Beta", again
we end up with an array of 1's, where the cell is "Beta", 0's where
it is not.

So, we now have two 'matching' arrays of 0's and 1's.

If we multiply each element of the array (your original question), we
get a series of...

1*1 = 1 (Where "Joe" is TRUE (1) AND "Beta" is TRUE (1))
1*0 = 0 (Where "Joe" is TRUE (1) AND "Beta" is FALSE (0))
0*1 = 0 (Where "Joe" is FALSE (0) AND "Beta" is TRUE (1))

SUMPRODUCT simply now takes these results 1,0,1,0,0,0,1,0 and SUMS
them. Effecting a count of the 1's.

That's probably as clear as mud!

No, it does make sense.

Read the web page will probably do

more for you.

Will do.

It can also be used for multiple SUMIF criteria and

bear in mind, Excel 2007 has a few multi-criteria ones build in

SUMIFS, COUNTIFS, AVERAGEIFS...

Thanks again, and I'll have a play with those various functions.


Django
Sotonian Born and Bred, once of Chandlers Ford, now in Derbyshire.

--

 

[Nick Hodge]

Actually, I am Chandlers Ford too, just few people know where that is!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[Bob Phillips]

They do, they just don't admit it!

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)