Count Using Multiple Criteria
 

Hi, I'm trying to prepare a summary worksheet to count where
B2:B22 = "associate"
and
C2:C22 contains a date (or is not blank)

I'm sure it's doable but I'm stumped. Thanks.

Nicole

Count Using Multiple Criteria
 

=SUMPRODUCT(--(B2:B22="associate"),--(C2:C22<""))

--


Regards,


Peo Sjoblom

"Nicole Bradshaw" <Nicole wrote in
message ...
Hi, I'm trying to prepare a summary worksheet to count where
B2:B22 = "associate"
and
C2:C22 contains a date (or is not blank)

I'm sure it's doable but I'm stumped. Thanks.

Nicole

Count Using Multiple Criteria
 

You can use =sumproduct(--(b2:b22="associate"),--(c2:c22="your date
condition"),--(c2:c22<""))

Enter this with CTRL-SHIFT-ENTER

I am not sure how to test a cell for a date...

"Nicole Bradshaw" wrote:

Hi, I'm trying to prepare a summary worksheet to count where
B2:B22 = "associate"
and
C2:C22 contains a date (or is not blank)

I'm sure it's doable but I'm stumped. Thanks.

Nicole

Count Using Multiple Criteria
 

=SUMPRODUCT(--( B2:B22 = "associate"),--(C2:C22<""))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Nicole Bradshaw" <Nicole wrote in
message ...
Hi, I'm trying to prepare a summary worksheet to count where
B2:B22 = "associate"
and
C2:C22 contains a date (or is not blank)

I'm sure it's doable but I'm stumped. Thanks.

Nicole

Count Using Multiple Criteria
 

No need to enter with ctrl + shift & enter

--


Regards,


Peo Sjoblom

"Sheeloo" wrote in message
...
You can use =sumproduct(--(b2:b22="associate"),--(c2:c22="your date
condition"),--(c2:c22<""))

Enter this with CTRL-SHIFT-ENTER

I am not sure how to test a cell for a date...

"Nicole Bradshaw" wrote:

Hi, I'm trying to prepare a summary worksheet to count where
B2:B22 = "associate"
and
C2:C22 contains a date (or is not blank)

I'm sure it's doable but I'm stumped. Thanks.

Nicole

Count Using Multiple Criteria
 

Thanks -- just so I understand, what does -- represent? My formula still
isn't working correctly....


"Peo Sjoblom" wrote:

=SUMPRODUCT(--(B2:B22="associate"),--(C2:C22<""))

--


Regards,


Peo Sjoblom

"Nicole Bradshaw" <Nicole wrote in
message ...
Hi, I'm trying to prepare a summary worksheet to count where
B2:B22 = "associate"
and
C2:C22 contains a date (or is not blank)

I'm sure it's doable but I'm stumped. Thanks.

Nicole

Count Using Multiple Criteria
 

Just turn Boolean values into 1 or 0 so one can use the built in format of
SUMPRODUCT

If it didn't work then you have data that is not what it looks like.

Are the blank cells empty or do you have a formula and if you do how does
one formula that returns a blank look like?

You might have leading or trailing spaces in B2:B22

Can C2:C22 only hold dates are blanks than you can use

=SUMPRODUCT(--(B2:B22="associate"),--(ISNUMBER(C2:C22)))


there is nothing wrong with the formula, also be specific. What does not
work?




--


Regards,


Peo Sjoblom

"Nicole Bradshaw" wrote in
message ...
Thanks -- just so I understand, what does -- represent? My formula still
isn't working correctly....


"Peo Sjoblom" wrote:

=SUMPRODUCT(--(B2:B22="associate"),--(C2:C22<""))

--


Regards,


Peo Sjoblom

"Nicole Bradshaw" <Nicole wrote in
message ...
Hi, I'm trying to prepare a summary worksheet to count where
B2:B22 = "associate"
and
C2:C22 contains a date (or is not blank)

I'm sure it's doable but I'm stumped. Thanks.

Nicole

Count Using Multiple Criteria
 

Can someone tell me what the "--" does in a function? Thanks to all of you
for your help with this!

"Sandy Mann" wrote:

=SUMPRODUCT(--( B2:B22 = "associate"),--(C2:C22<""))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Nicole Bradshaw" <Nicole wrote in
message ...
Hi, I'm trying to prepare a summary worksheet to count where
B2:B22 = "associate"
and
C2:C22 contains a date (or is not blank)

I'm sure it's doable but I'm stumped. Thanks.

Nicole

Count Using Multiple Criteria
 

-- converts TRUE or FALSE to 1 or 0 respectively

"Nicole Bradshaw" wrote:

Thanks -- just so I understand, what does -- represent? My formula still
isn't working correctly....


"Peo Sjoblom" wrote:

=SUMPRODUCT(--(B2:B22="associate"),--(C2:C22<""))

--


Regards,


Peo Sjoblom

"Nicole Bradshaw" <Nicole wrote in
message ...
Hi, I'm trying to prepare a summary worksheet to count where
B2:B22 = "associate"
and
C2:C22 contains a date (or is not blank)

I'm sure it's doable but I'm stumped. Thanks.

Nicole

Count Using Multiple Criteria
 

Thanks for pointing that out...

"Peo Sjoblom" wrote:

No need to enter with ctrl + shift & enter

--


Regards,


Peo Sjoblom

"Sheeloo" wrote in message
...
You can use =sumproduct(--(b2:b22="associate"),--(c2:c22="your date
condition"),--(c2:c22<""))

Enter this with CTRL-SHIFT-ENTER

I am not sure how to test a cell for a date...

"Nicole Bradshaw" wrote:

Hi, I'm trying to prepare a summary worksheet to count where
B2:B22 = "associate"
and
C2:C22 contains a date (or is not blank)

I'm sure it's doable but I'm stumped. Thanks.

Nicole

Count Using Multiple Criteria
 

Thanks -- the formula was correct, I had the parens in wrong place. I've got
it now -- I need to take an advanced Excel course so I understand what I'm
doing. Thanks again for your quick help!

N

"Peo Sjoblom" wrote:

Just turn Boolean values into 1 or 0 so one can use the built in format of
SUMPRODUCT

If it didn't work then you have data that is not what it looks like.

Are the blank cells empty or do you have a formula and if you do how does
one formula that returns a blank look like?

You might have leading or trailing spaces in B2:B22

Can C2:C22 only hold dates are blanks than you can use

=SUMPRODUCT(--(B2:B22="associate"),--(ISNUMBER(C2:C22)))


there is nothing wrong with the formula, also be specific. What does not
work?




--


Regards,


Peo Sjoblom

"Nicole Bradshaw" wrote in
message ...
Thanks -- just so I understand, what does -- represent? My formula still
isn't working correctly....


"Peo Sjoblom" wrote:

=SUMPRODUCT(--(B2:B22="associate"),--(C2:C22<""))

--


Regards,


Peo Sjoblom

"Nicole Bradshaw" <Nicole wrote in
message ...
Hi, I'm trying to prepare a summary worksheet to count where
B2:B22 = "associate"
and
C2:C22 contains a date (or is not blank)

I'm sure it's doable but I'm stumped. Thanks.

Nicole

Count Using Multiple Criteria
 

Hi,

If you reread the responses its there.

The portions of the formula that read

B2:B22 = "associate"

return a series of TRUE's and FALSE's even if you don't see them.

when you add a minus sign in front of this:
-(B2:B22 = "associate")
Excel change the TRUE's to -1's and the FALSE's to 0's
But we don't want negative 1's so we convert the negatives back to positive
by
--(B2:B22 = "associate")
The SUMPRODUCT function multiplies all the 0's and 1's times each other, 0*0
= 0
1*1 = 1 . 1*0 = 0*1 = 0. So anytime all conditions are met we are
multiplying a series of 1's which is 1. Anytime any of the conditions are
not met we are multiplying something times 0 which is always 0. The
SUMPRODUCT sums the 0's and 1's to give us our count.

Hope this helps.

--
Thanks,
Shane Devenshire


"Nicole Bradshaw" wrote:

Can someone tell me what the "--" does in a function? Thanks to all of you
for your help with this!

"Sandy Mann" wrote:

=SUMPRODUCT(--( B2:B22 = "associate"),--(C2:C22<""))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Nicole Bradshaw" <Nicole wrote in
message ...
Hi, I'm trying to prepare a summary worksheet to count where
B2:B22 = "associate"
and
C2:C22 contains a date (or is not blank)

I'm sure it's doable but I'm stumped. Thanks.

Nicole

Count Using Multiple Criteria
 

Hi,

You can also try the following array formula (Ctrl+Shift+Enter)

COUNT(IF((B1:B5="Associate")*(ISNUMBER(C1:C5)),C1: C5))

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Nicole Bradshaw" <Nicole wrote in
message ...
Hi, I'm trying to prepare a summary worksheet to count where
B2:B22 = "associate"
and
C2:C22 contains a date (or is not blank)

I'm sure it's doable but I'm stumped. Thanks.

Nicole