Count Using Multiple Criteria

[Nicole Bradshaw]

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

 

[Peo Sjoblom]

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

--


Regards,


Peo Sjoblom

 

[Sheeloo]

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

 

[Sandy Mann]

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

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Peo Sjoblom]

No need to enter with ctrl + shift & enter

--


Regards,


Peo Sjoblom

 

[Nicole Bradshaw]

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

 

[Peo Sjoblom]

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]

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

 

[Sheeloo]

-- converts TRUE or FALSE to 1 or 0 respectively

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

 

[Sheeloo]

Thanks for pointing that out...

No need to enter with ctrl + shift & enter

--


Regards,


Peo Sjoblom

 

[Nicole Bradshaw]

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

 

[ShaneDevenshire]

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.

 

[Ashish Mathur]

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