Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count multiple cells against multiple criteria in an Excel spreads | Excel Worksheet Functions | |||
Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
Count multiple criteria | Excel Worksheet Functions | |||
COUNT WITH MULTIPLE CRITERIA | Excel Worksheet Functions | |||
COUNT or SUM with multiple criteria | Excel Worksheet Functions |