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



--

Django Cat

How about

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk





"Django Cat" <notareal@address.co.uk> wrote in message
news:zhQXj.39910$66.7457@newsfe20.ams2...
> 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 wrote:

> =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

--

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
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk





"Django Cat" <notareal@address.co.uk> wrote in message
news:UiSXj.39921$66.1664@newsfe20.ams2...
> Nick Hodge wrote:
>
>> =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 wrote:

> From: "Nick Hodge" <nick_hodge@zen.co.uk>
> 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.

--

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk





"Django Cat" <notareal@address.co.uk> wrote in message
news:dxTXj.15913$ie5.13853@newsfe05.ams2...
> Nick Hodge wrote:
>
>> From: "Nick Hodge" <nick_hodge@zen.co.uk>
>> 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.
>
> --
>

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)

"Nick Hodge" <nick_hodge@zen.co.uk> wrote in message
news:OpjtHAOuIHA.748@TK2MSFTNGP05.phx.gbl...
> Actually, I am Chandlers Ford too, just few people know where that is!
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
> web: www.excelusergroup.org
> web: www.nickhodge.co.uk
>
>
>
>
>
> "Django Cat" <notareal@address.co.uk> wrote in message
> news:dxTXj.15913$ie5.13853@newsfe05.ams2...
>> Nick Hodge wrote:
>>
>>> From: "Nick Hodge" <nick_hodge@zen.co.uk>
>>> 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.
>>
>> --
>>