COUNTIF (?) on two criteria

D

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



--
 
D

Django Cat

Nick said:
=SUMPRODUCT((A1:A17="Joe")*(B1:B17="Beta"))
Click to expand...

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

--
 
N

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
 
D

Django Cat

Nick said:
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!
Click to expand...

No, it does make sense.

Read the web page will probably do
more for you.
Click to expand...

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...
Click to expand...

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


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

--
 
B

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting on two Criteria 3
CountIf with multiple criteria 2
Multiple criteria for Countif 1
Count items based on two criteria 7
Windows XP Countif with Multiple Criteria 1
COUNTIF, Sorting, on Two Sheets 1
countif with two criteria one a column of dates? 3
COUNTIF statement with 3 criteria checks 4

Top