Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct(?)
Hello. I want to count the dates in column B where the corresponding value
in column A is 'Sched'. Expecting the answer to be: 2 for the example below. I've tried to use and array (which i rarely use), i've also looked into SUMPRODUCT (which i've never used). I haven't had any luck with either. Can anyone help please? A B 1 Sched 2/15/2007 2 2/19/2007 3 Sched 3/1/2007 4 Sched 5 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct(?)
Try this:
=SUMPRODUCT(--(A1:A10="Sched"),--(ISNUMBER(B1:B10))) Note: you can't use entire columns as range references (unless you're using Excel 2007). -- Biff Microsoft Excel MVP "Suzanne" wrote in message ... Hello. I want to count the dates in column B where the corresponding value in column A is 'Sched'. Expecting the answer to be: 2 for the example below. I've tried to use and array (which i rarely use), i've also looked into SUMPRODUCT (which i've never used). I haven't had any luck with either. Can anyone help please? A B 1 Sched 2/15/2007 2 2/19/2007 3 Sched 3/1/2007 4 Sched 5 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct(?)
If you want an array* formula, try this:
=SUM(IF((A1:A5="Sched")*(B1:B5<""),1) Strictly speaking, it only tests for cells in column B not being empty, not specifically for them containing a date (which is only a number to Excel). * As this is an array formula, then once you have typed it in (or subsequently edit it) you must commit it with CTRL-SHIFT-ENTER rather than the usual ENTER. If you do this correctly, then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. An alternative would be: =SUMPRODUCT((A1:A5="Sched")*(B1:B5<"")) Hope this helps. Pete On Jul 11, 6:30 pm, Suzanne wrote: Hello. I want to count the dates in column B where the corresponding value in column A is 'Sched'. Expecting the answer to be: 2 for the example below. I've tried to use and array (which i rarely use), i've also looked into SUMPRODUCT (which i've never used). I haven't had any luck with either. Can anyone help please? A B 1 Sched 2/15/2007 2 2/19/2007 3 Sched 3/1/2007 4 Sched 5 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct(?)
Thank you very much T. Valko, i wasn't aware of the column range limitation.
Appreciate it. Enjoy the day. "T. Valko" wrote: Try this: =SUMPRODUCT(--(A1:A10="Sched"),--(ISNUMBER(B1:B10))) Note: you can't use entire columns as range references (unless you're using Excel 2007). -- Biff Microsoft Excel MVP "Suzanne" wrote in message ... Hello. I want to count the dates in column B where the corresponding value in column A is 'Sched'. Expecting the answer to be: 2 for the example below. I've tried to use and array (which i rarely use), i've also looked into SUMPRODUCT (which i've never used). I haven't had any luck with either. Can anyone help please? A B 1 Sched 2/15/2007 2 2/19/2007 3 Sched 3/1/2007 4 Sched 5 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct(?)
=SUM(IF((A1:A5="Sched")*(B1:B5<""),1)
=SUM((A1:A5="Sched")*(B1:B5<"")) -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ups.com... If you want an array* formula, try this: =SUM(IF((A1:A5="Sched")*(B1:B5<""),1) Strictly speaking, it only tests for cells in column B not being empty, not specifically for them containing a date (which is only a number to Excel). * As this is an array formula, then once you have typed it in (or subsequently edit it) you must commit it with CTRL-SHIFT-ENTER rather than the usual ENTER. If you do this correctly, then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. An alternative would be: =SUMPRODUCT((A1:A5="Sched")*(B1:B5<"")) Hope this helps. Pete On Jul 11, 6:30 pm, Suzanne wrote: Hello. I want to count the dates in column B where the corresponding value in column A is 'Sched'. Expecting the answer to be: 2 for the example below. I've tried to use and array (which i rarely use), i've also looked into SUMPRODUCT (which i've never used). I haven't had any luck with either. Can anyone help please? A B 1 Sched 2/15/2007 2 2/19/2007 3 Sched 3/1/2007 4 Sched 5 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct(?)
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Suzanne" wrote in message ... Thank you very much T. Valko, i wasn't aware of the column range limitation. Appreciate it. Enjoy the day. "T. Valko" wrote: Try this: =SUMPRODUCT(--(A1:A10="Sched"),--(ISNUMBER(B1:B10))) Note: you can't use entire columns as range references (unless you're using Excel 2007). -- Biff Microsoft Excel MVP "Suzanne" wrote in message ... Hello. I want to count the dates in column B where the corresponding value in column A is 'Sched'. Expecting the answer to be: 2 for the example below. I've tried to use and array (which i rarely use), i've also looked into SUMPRODUCT (which i've never used). I haven't had any luck with either. Can anyone help please? A B 1 Sched 2/15/2007 2 2/19/2007 3 Sched 3/1/2007 4 Sched 5 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct(?)
Thank you, as i'm turning the spreadsheet over to a less experienced group,
was trying to stear clear of an array. Not that the one i tried to use worked : ) I appreciate one that does and will save for future use. Cheers. "Pete_UK" wrote: If you want an array* formula, try this: =SUM(IF((A1:A5="Sched")*(B1:B5<""),1) Strictly speaking, it only tests for cells in column B not being empty, not specifically for them containing a date (which is only a number to Excel). * As this is an array formula, then once you have typed it in (or subsequently edit it) you must commit it with CTRL-SHIFT-ENTER rather than the usual ENTER. If you do this correctly, then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. An alternative would be: =SUMPRODUCT((A1:A5="Sched")*(B1:B5<"")) Hope this helps. Pete On Jul 11, 6:30 pm, Suzanne wrote: Hello. I want to count the dates in column B where the corresponding value in column A is 'Sched'. Expecting the answer to be: 2 for the example below. I've tried to use and array (which i rarely use), i've also looked into SUMPRODUCT (which i've never used). I haven't had any luck with either. Can anyone help please? A B 1 Sched 2/15/2007 2 2/19/2007 3 Sched 3/1/2007 4 Sched 5 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct(?)
Thanks for the feedback. Now that Biff has shown you an alternative,
you can see the similarity between it and the SUMPRODUCT version - some prefer the latter because you don't have to use CSE to commit. Pete On Jul 11, 7:24 pm, Suzanne wrote: Thank you, as i'm turning the spreadsheet over to a less experienced group, was trying to stear clear of an array. Not that the one i tried to use worked : ) I appreciate one that does and will save for future use. Cheers. "Pete_UK" wrote: If you want an array* formula, try this: =SUM(IF((A1:A5="Sched")*(B1:B5<""),1) Strictly speaking, it only tests for cells in column B not being empty, not specifically for them containing a date (which is only a number to Excel). * As this is an array formula, then once you have typed it in (or subsequently edit it) you must commit it with CTRL-SHIFT-ENTER rather than the usual ENTER. If you do this correctly, then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. An alternative would be: =SUMPRODUCT((A1:A5="Sched")*(B1:B5<"")) Hope this helps. Pete On Jul 11, 6:30 pm, Suzanne wrote: Hello. I want to count the dates in column B where the corresponding value in column A is 'Sched'. Expecting the answer to be: 2 for the example below. I've tried to use and array (which i rarely use), i've also looked into SUMPRODUCT (which i've never used). I haven't had any luck with either. Can anyone help please? A B 1 Sched 2/15/2007 2 2/19/2007 3 Sched 3/1/2007 4 Sched 5 - Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT has never let me down BUT... | Excel Worksheet Functions | |||
Please help w/Sumproduct! | Excel Worksheet Functions | |||
Using SumProduct in VB | Excel Discussion (Misc queries) | |||
Sumproduct..help please | Excel Worksheet Functions | |||
now() sumproduct. | Excel Worksheet Functions |